Top 1 for multiple records

[jpanalyst]

I need to run a query that will select the top field in each case. I have repeat entries of an ID with a count but I would like it to select one of those entries at random rather than selecting all of them.

e.g.
My table looks like this:-
ID: 00001 Count: 6 Place: New York
ID: 00001 Count: 6 Place: Chicago
ID: 00002 Count: 4 Place: New York
ID: 00002 Count: 4 Place: Chicago

I only want 1 entry from each ID.

Thanks

 

[ajetrumpet]

it would be helpful to people if you uploaded the DB here (pre-2007 version please)

 

[jsv2002]

Also why more than 1 unique ID, also nothing is truly random with databases..

 

[ajetrumpet]

also nothing is truly random with databases..

i'm sorry, that's not true.

 

[jsv2002]

"i'm sorry, that's not true."

Sorry it is... just not found the bug yet..

By definition a database has "data" which it gets its results from so its not random you just asked the wrong question...

 

[ajetrumpet]

"i'm sorry, that's not true."

Sorry it is... just not found the bug yet..

By definition a database has "data" which it gets its results from so its not random you just asked the wrong question...

lets get back to helping people instead of arguing about this. thanks.

 

[ByteMyzer]

Try the following SQL (substitute highlighted text with actual table/field names):

SELECT T1.[b][i]ID[/i][/b], (
                SELECT TOP 1 T2.[b][i]Place[/i][/b]
                FROM [b][i]MyTable[/i][/b] AS T2
                WHERE T2.[b][i]ID[/i][/b] = T1.[b][i]ID[/i][/b]
                ORDER BY RND(ASC(T2.[b][i]Place[/i][/b]))
              ) AS [Place]
FROM [b][i]MyTable[/i][/b] AS T1
GROUP BY T1.[b][i]ID[/i][/b]

 

[jsv2002]

lets get back to helping people instead of arguing about this. thanks.

Ahh ok thought that was what I was doing in the first place...