Return only one record from table with repeating values in fields

[Beemermonkey]

I have a table like this

Stuff.
ItemNo     Name      ImageName
123          Foo        00123.jog
123          Foo        00FOO.jpg
123          FOO       FOO123.jpg
456          bar        00456.jog
456          BAR        00BAR.jpg
456          Bar       BAR456.jpg
...

I want to do a query to return just one row per unique ItemNo

So the query would return
ItemNo Name ImageName
123 Foo 00123.jog
456 bar 00456.jog

I don't care which one it grabs, the first is as good as the last, as they are essentially different names for the same thing coming from different systems.

I know there's gotta be an easy way to do this, but I've tried things like TOP, DISTINCT, etc and none of them work for me.

 

[pbaldy]

Try

SELECT ItemNo, Max(Name), Max(ImageName)
FROM TableName
GROUP BY ItemNo

 

[Beemermonkey]

Thanks

Try

SELECT ItemNo, Max(Name), Max(ImageName)
FROM TableName
GROUP BY ItemNo

Heh, I found the trail just before you posted this response!

It took me awhile because I kept trying to set the First() thing on the ItemNo since in my mind that's what I wanted.

Although I used First(X), and i ran into another problem because one of the fields in my real data is an expression, so i had to choose expression from the Total drop down to get it not to spit out an error.

You're busy on here aren't you? Thanks!

 

[Pat Hartman]

In this situation, First()/Last() is a better choice than Max()/Min() because with First()/Last() you'll get two values from the same record but with Max()/Min() you could get a Name from one record but an ImageName from a different record.