I was adivsed to post my question here so here we go:

Im trying to create a photo album where pictures are ordered by date inside each album. In the database I have three columns: FileName, Album, Date. (Date=time when inserted into database)  I have the first part down by using:

"SELECT * from pictures ORDER BY album, date"

But now I want to order these albums after where last picture added. I have 3 albums: Home, Vaccation, Work. If I add a picture to the Work Album I want the work Album pictures to come first (ordered by date) and then comes the album where previous picture was added and so on.

Limno suggested this:

limno:

view plaincopy to clipboardprint?

1. declare @pictures table (id int identity(1,1),  
2. FileName varchar(100),   
3. Album varchar(100),   
4. Date datetime)  
5. insert into @pictures values ('a1', 'Home','1/1/2009')  
6. insert into @pictures values ('a11', 'Home','1/2/2009')  
7. insert into @pictures values ('b1', 'Vaccation','1/1/2009')  
8. insert into @pictures values ('b11', 'Vaccation','1/2/2009')  
9. insert into @pictures values ('c1', 'Work','1/1/2009')  
10. insert into @pictures values ('a111', 'Home','1/3/2009')  
11. insert into @pictures values ('a1111', 'Home','1/4/2009')  
12. insert into @pictures values ('c11', 'Work','1/2/2009')  
13. insert into @pictures values ('c111', 'Work','1/3/2009')  
14. insert into @pictures values ('c1111', 'Work','1/4/2009')  
15. insert into @pictures values ('b111', 'Vaccation','1/3/2009')  
16. insert into @pictures values ('b1111', 'Vaccation','8/4/2009')  
17. insert into @pictures values ('c1c', 'Work','1/1/2009')  
18. insert into @pictures values ('c1c', 'Work','9/1/2009')  
19.   
20.   
21. SELECT FileName ,Album ,Date FROM (SELECT FileName ,Album ,Date,   
22. MAX(date) OVER(PARTITION BY Album) as myOrder FROM @pictures) t  
23. ORDER BY myOrder DESC, Album, Date DESC  

declare @pictures table (id int identity(1,1),
FileName varchar(100), 
Album varchar(100), 
Date datetime)
insert into @pictures values ('a1', 'Home','1/1/2009')
insert into @pictures values ('a11', 'Home','1/2/2009')
insert into @pictures values ('b1', 'Vaccation','1/1/2009')
insert into @pictures values ('b11', 'Vaccation','1/2/2009')
insert into @pictures values ('c1', 'Work','1/1/2009')
insert into @pictures values ('a111', 'Home','1/3/2009')
insert into @pictures values ('a1111', 'Home','1/4/2009')
insert into @pictures values ('c11', 'Work','1/2/2009')
insert into @pictures values ('c111', 'Work','1/3/2009')
insert into @pictures values ('c1111', 'Work','1/4/2009')
insert into @pictures values ('b111', 'Vaccation','1/3/2009')
insert into @pictures values ('b1111', 'Vaccation','8/4/2009')
insert into @pictures values ('c1c', 'Work','1/1/2009')
insert into @pictures values ('c1c', 'Work','9/1/2009')


SELECT FileName ,Album ,Date FROM (SELECT FileName ,Album ,Date, 
MAX(date) OVER(PARTITION BY Album) as myOrder FROM @pictures) t
ORDER BY myOrder DESC, Album, Date DESC

My probem now is that Im running MySQL. I created the database as posted above but Im having problem with converting the SELECT statement. More specifically "MAX(date) OVER (PARTITION BY Album)"

I looked at some links: http://forums.mysql.com/read.php?32,225340,225340

http://www.xaprb.com/blog/2005/09/27/simulating-the-sql-row_number-function/

But I dont know how to implement this into my query to solve this issue. Any help is appreciated! :)

Boddam:

More specifically "MAX(date) OVER (PARTITION BY Album)"

I dont know 'Over' is supported in MYSQL or not.

You can try this query.

Select *,(Select MAX(date) from MyTable where Album=M.Album group by Album)
as MaxDate from MyTable M order by MaxDate desc

Where MyTable is your table name

Regard

Imrankhan

imran_khan:

Boddam:

More specifically "MAX(date) OVER (PARTITION BY Album)"

I dont know 'Over' is supported in MYSQL or not.

You can try this query.

Select *,(Select MAX(date) from MyTable where Album=M.Album group by Album)
as MaxDate from MyTable M order by MaxDate desc

Where MyTable is your table name

Regard

Imrankhan

That was BEAUTIFUL! I thank you :)