Selecting first of a series

[AEChris]

I have a table of image paths and descriptions. There can be duplicate values in the image path field with different descriptions of each.

ie)
monkey.jpg | Monkey on a tree
monkey.jpg | Monkey sitting in a tree
car.jpg | A red car
car.jpg | A sweet red car

Now my question is, how can I select just the first image path and description if there is multiple image paths, does this make sense? In the example above, I just want to select "monkey.jpg" with "Monkey on a tree" and "car.jpg" with "A red car".

Any help would be greatly appreciated!

Chris

 

[gcole]

Make sure you use an id field so you know that order in which the records were entered.
Then try:

SELECT TOP 1 *
FROM table
order by id desc (if you want the most recient entry).

 

[tlbroadbent]

I agree that an ID (or insert date) on the record is the best solution for selecting the "first" record for the image. SQL has no real concept of first or last. It does have minimum and maximum functions. Use these to select one row if the ID or date column doesn;t exist.

Select
ImagePath,
ImageDescription=Min(ImageDescription)
From Table_name
Group By ImagePath Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[MadhanaGopal]

Hi Terry L. Broadbent
I think there is no way to choose first entry of each image path.There is a function called Firstof and Lastof in Access.It fullfills the above requirement.There is no equallent function in SQL Server.

Select
ImagePath,
ImageDescription=Min(ImageDescription)
From Table_name
Group By ImagePath

This Query gives the result.But it is not sure the result is always correct.It gives the Minimum of the ImageDescription not a First Value of the Original Result.Pl. Check it.
P.Madhana Gopal.
Principal Software Engineer
LCube Innovative Solutions Pvt. Ltd.
India

 

[tlbroadbent]

MadhanaGopal,

Please read my post. It says the same thing that you said. I fully understand that First and Min are not necessarily equivalent.

I also understand that First is a NON ANSI extension in MS Access that doesn't exist in T-SQL. That is why I said that the ID or a date column are needed to provide the ability to select the "first" record. Selecting the MIN of these values is equivalent to First because the first record stored would have the minimum value.

In the absence of a column that could be used to determine which row was stored first, the developer must make a choice - ALL values , MIN values, MAX values, or perhaps develop another method for choosing a row.

I hope this clarifies my point. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[MadhanaGopal]

Yes.u r correct.