Advanced ORDER BY, is it possible?

[bijtvuur]

I have trouble getting the data in the right order from a table with the columns Date and City:

APRIL 8 2010 | AMSTERDAM
APRIL 9 2010 | ZAGREB
APRIL 10 2010 | AMSTERDAM
APRIL 11 2010 | NEW YORK
APRIL 15 2010 | NEW YORK

I want to have the record with the nearest Date in the future on the top. Then I want to display all the other records with the same City under it ordered by date. Next I want to have the next city with the nearest date ... and so on.

So the result should be:

APRIL 8 2010 | AMSTERDAM
APRIL 10 2010 | AMSTERDAM
APRIL 9 2010 | ZAGREB
APRIL 11 2010 | NEW YORK
APRIL 15 2010 | NEW YORK

Who can help me with a single select statement to get this?

I am lost

 

[DCrake]

What happens if you have to cities on the same date?

 

[bijtvuur]

In that case it is for me not really important which City then gets the priority, as long as they are not mixed up. So first City 1 and then City 2. And if I have to choose, I prefer alfabetical order.

 

[namliam]

YOu need to get a query first:
Select city, Min(Yourdate) as Firstdate from yourtable

Save this query in your DB, then make a second query getting your original data and joining th cities. Then sort first on the (hidden) FirstDate, then City then Date.

 

[DCrake]

Without testing it and not knowing what you have tried it looks like by date by city

 

[bijtvuur]

Thanks namlian!

Is the suggestion of namlian the only option to solve this? I prefer to not do this, but if it is the only way, I have to do it.

Or is still a single select statement possible?

 

[FoFa]

If it is a date field, can't you order by City, then date descending?

 

[bijtvuur]

It isn't that simple... because then you get AMSTERDAM, NEW YORK and then ZAGREB and that is not the right sequence because ZAGREB should appear before NEW YORK because it has a nearer Date.

 

[namliam]

Thanks namlian!

Is the suggestion of namlian the only option to solve this? I prefer to not do this, but if it is the only way, I have to do it.

Or is still a single select statement possible?

If you are comfirtable with the more complex SQL statements including sub-selects, yes highly possible.
You can make SQL statements in 100 queries or one query, just depends on the level of depth you want to go. The way I discribed it is 2 "stored" queries but still executes quite comfirtably as one query wihtout problems.

As I understand your issue... given this table, order by date...

DateField	CityName
01/04/2009	Zandvoort
01/01/2010	amsterdam
02/15/2010	Groningen
03/15/2010	Groningen
05/01/2010	amsterdam
10/01/2010	Zandvoort

You want this output:

DateField	CityName
01/04/2009	Zandvoort
10/01/2010	Zandvoort
01/01/2010	amsterdam
05/01/2010	amsterdam
02/15/2010	Groningen
03/15/2010	Groningen

Now the first sql:

SELECT YourTable.CityName, Min(YourTable.DateField) AS MinOfDateField
FROM YourTable
GROUP BY YourTable.CityName;

The Second:

SELECT YourTable.DateField, YourTable.CityName
FROM YourTable INNER JOIN [COLOR="Blue"]Query1[/COLOR] ON YourTable.CityName = Query1.CityName
ORDER BY Query1.MinOfDateField, YourTable.CityName, YourTable.DateField;

This second query gives the "proper" result as I unserstand it.

If you want to "force" only one query....

SELECT YourTable.DateField, YourTable.CityName
FROM YourTable INNER JOIN (
    [COLOR="Red"]SELECT YourTable.CityName, Min(YourTable.DateField) AS  MinOfDateField
    FROM YourTable
    GROUP BY YourTable.CityName[/COLOR]) as [COLOR="blue"]Query1[/COLOR] ON YourTable.CityName = Query1.CityName
ORDER BY Query1.MinOfDateField, YourTable.CityName, YourTable.DateField;

This "forced" query also gives the same result.

Notice that the Red sub-query is simply a copy of Query1, that is in the same SQL and then "aliased" as Query1
It is just that the "one" query is a little harder to maintain/understand than the split out 2 queries... in particular to the "less experieced" SQL user.

Regards

Your Mailman

 

[bijtvuur]

Great, that was what I was looking for!

It is now working (with a little trial and error), but in my situation there are two tables involved. One with the Dates and one with the Cities. Is there a way to join those two tables? I have done it now the quick and dirty way with 5 individual select statements to get the data.

Because every other method I try will result in syntax errors.

 

[namliam]

It is now working (with a little trial and error), but in my situation there are two tables involved. One with the Dates and one with the Cities. Is there a way to join those two tables? I have done it now the quick and dirty way with 5 individual select statements to get the data.

well, this is your own question to which you must supply the answer yourself...

I dont know if therye is any way to join the tables? Do they have simular fields that could allow them to be joined?

Really the easiest way is to just do it query by query like I showed you, then "roll them up" like I did.

 

[bijtvuur]

It is possible to join the tables because there is a related id in one of the tables. But a JOIN or table1.id = table2.id didn't work, I get syntax errors. But I think I will let it this way, it works great now!

Thanks,
Vncent

 

[namliam]

Well anywhere in the SQL where I have "YourTable" you must then replace by the 2 tables:
From YourTable1
Join YourTable2 on YourTable1.ID = YourTable2.ID

 

[bijtvuur]

Yes, I know... it always works, but in this situation it wouldn't. Ah, no prob... I wil let it like it is now.

Thank you for your support namliam!