CODEWITHSUNDEEP
mysqlsqlsql-serverms-access
Omar Juvera
Omar Juvera

Reputation: 12297

MYSQL equivalent of MS-Access function first() last()

To better explain what I need please look at this table:

ID --- image
A1 --- a1_01.jpg
A1 --- a1_02.jpg
B7 --- b7_01.jpg
B7 --- b7_02.jpg
D3 --- D3_04.jpg
D3 --- D3_99.jpg
... (From A to Z)
Z9 --- Z9_12.jpg
Z9 --- Z9_13.jpg
Z9 --- Z9_20.jpg
Z9 --- Z9_99.jpg
...and so on

I need the results to be:

ID --- Image
A1 --- a1_01.jpg
B7 --- b7_01.jpg
D3 --- D3_04.jpg
...
Z9 --- Z9_12.jpg
...and so on

In MS-Access this is can be done with SELECT ID, First(image) AS 'Image' FROM Photos

How can I accomplish this in MYSQL?

Remember that this is a mockup table, to illustrate my problem. I have tons and tons of records. IT IS NOT a single (least/most) value!

===EDIT: 11/24/2012===
FINAL SQL CODE: 

SELECT ID, Min(image) AS 'Image' 
FROM Photos 
GROUP BY ID

Thank you @triclosan & @iDevlop !!!

Upvotes: 0

Views: 2162

Answers (3)

fthiella
fthiella

Reputation: 49069

If the first image is also the image with the minimum value, and the last image is also the image with the maximum value, this MySql query returns exactly what you want:

Select ID, min(image) as first_image, max(image) as last_image
From Tbl
Group By ID

otherwise, since MySql has no first() or last() aggregate function, you could use something like this:

Select
  Distinct ID,
  (select image from tbl a where a.ID = tbl.ID order by something ASC LIMIT 1) as first_image,
  (select image from tbl a where a.ID = tbl.ID order by something DESC LIMIT 1) as last_image,
From tbl

but notice that you have to order your table by some field. If you can't order your table by some field, there's no way to tell which value comes first and which one last.

I often see this query, that looks nice and that is supposed to return the first field:

Select ID, image
From tbl
Group by ID

this a non-standard Sql query that usually works and (almost?) always returns correctly the first image. But since image is not in the group by clause and is not associated to any aggregate function, the server is free to return any value. It seems that it always returns the first value, but it's not guaranteed and I prefer not to use it.

Some explanations:

In your example, the minimum always coindices with the first, so there's no way to tell from your example if you are looking for the minimum or if you are looking for the first.

Suppose this is your data:

ID  image
=============
A1  a1_02.jpg
A1  a1_01.jpg

If you are looking for the minimum (A1, a1_01.jpg), min(image) is exactly what you need and it will work!

But if you are trying to get A1, a1_02.jpg, which in this example is the first... then we have a problem. Without a specific ORDER BY, the rows will be returned in an unpredictable way. Yes, they will often/almost always be returned in the order they are physically in the tablespace, but we can't rely on this.

See for example this question and the answers:

SQL best practice to deal with default sort order

This means that if we don't know how the rows are ordered, we can't tell which one is the first, and which the last. See for example this query:

Select
  Distinct ID,
 (select image from tbl a where a.ID = tbl.ID LIMIT 1) as first_image
From tbl

This will probably return what you are looking for. But since I omitted the ORDER BY clause, there's no guarantee that it will always work. And notice that i also had to remove last_image, because there's no way to tell MySql to order the table by default order DESC, since such default order doesn't exist.

This query will probably also return the first image:

Select ID, image
From tbl
Group By ID

In standard SQL we cannot refer to nonaggregated columns in the select, but in MySql it is possible. And it will probably return the first image for every ID. But let's see the documentation:

http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

"The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate." So the fact that the server almost always returns the first row is just coincidence, and it's not documented.

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

SELECT ID, Min(image) AS 'Image' FROM mockup GROUP BY ID

Upvotes: 1

triclosan
triclosan

Reputation: 5714

As i may see 

select ID, min(image)
from tbl
group by ID

Upvotes: 2

Related Questions