Query to select distinct field(but display entire record) 1

[jackiev]

This is what I have:
TABLE: INFO
Name Val1 Val2 Val3
Jim 1 2 3
Jim 4 7 9
Jim 3 7 2
Kate 1 2 8
Sally 3 5 4
Ann 3 2 1

I want to select the FIRST record for each name, & get all the fields of the record, sort by name. My guess would be :
Select Distinctrow Name, Val1, Val2, Val3 from INFO order by name. BUT that gets me all 6 records (Jim's name 3 x)
I want to get :
Ann 3 2 1
Jim 1 2 3
Kate 1 2 8
Sally 3 5 4
What do I need to do please?

 

[scriverb]

Here is an example that I believe will work for you.

Select A.Name, first(A.Val1) as Val1, First(A.Val2) as Val2, First(A.Val3) as Val3 FROM [i]yourtablename[/i] as A 
GROUP BY A.Name 
Order by A.Name;

Post back with questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[flyover789]

assuming, you want to show the first value in the Val1 field, you can try:

Select Name, first(Val1), Val2, Val3 from INFO group by Name, Val1, Val2, Val3 order by name

hth,
fly

Martin Serra Jr.

http://www.mserrasystems.com

 

[flyover789]

just forget my post ... look at Bob's

bye,
fly

Martin Serra Jr.

http://www.mserrasystems.com

 

[jackiev]

That was exactly what I needed. Thanks!!