Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sort query

Author  Topic 

IlyaIlya
Starting Member

5 Posts
Posted - 2009-09-21 : 14:52:44
Hi!

There is interesting problem that I cannot solve.

Given one table:

alias | date
-----------------
alias1 | 2009-06-08
alias1 | 2009-07-23
alias2 | 2009-06-05
alias2 | 2009-06-09
alias3 | 2009-09-23
alias3 | 2009-09-21
alias3 | 2009-01-21

I need to print rows with unique aliases in alphabetic order and then print all other rows in arbitrary order.

So, the next rows should be result of the query:

alias | data
-----------------
alias1 | 2009-07-23
alias2 | 2009-06-05
alias3 | 2009-09-23
alias2 | 2009-06-09
alias1 | 2009-06-08
alias3 | 2009-09-21
alias3 | 2009-01-21

How can I do that?

Thanks a lot.

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-09-21 : 15:15:26
Use two queries and combine them with a UNION.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

IlyaIlya
Starting Member

5 Posts
Posted - 2009-09-21 : 15:42:02
What are these two queries?
As I understood first query is
SELECT * FROM `ALIAS` GROUP BY alias;

And how can I obtain all other rows?
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-09-21 : 17:15:22
What are "unique" aliases?


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-09-21 : 17:41:26
try this:

select alias, date from <your table>
order by rank() over (partition by alias order by date)

Be One with the Optimizer
TG
Go to Top of Page

IlyaIlya
Starting Member

5 Posts
Posted - 2009-09-22 : 00:13:13
quote:
Originally posted by X002548

What are "unique" aliases?

Sorry, there were slightly incorrect words. I meant the next: I need to select one row per each alias and it's unimportant what dates would be in these rows.

Sorry for my bad English :-)
Go to Top of Page

IlyaIlya
Starting Member

5 Posts
Posted - 2009-09-22 : 00:13:56
quote:
Originally posted by TG

try this:

select alias, date from <your table>
order by rank() over (partition by alias order by date)

Be One with the Optimizer
TG


Thank you, I will try
Go to Top of Page

IlyaIlya
Starting Member

5 Posts
Posted - 2009-09-22 : 01:09:53
quote:
Originally posted by TG

try this:

select alias, date from <your table>
order by rank() over (partition by alias order by date)



Unfortunately, it doesn't work. I use MySQL that doesn't support rank()
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-09-22 : 03:01:57
quote:
Originally posted by IlyaIlya

quote:
Originally posted by TG

try this:

select alias, date from <your table>
order by rank() over (partition by alias order by date)



Unfortunately, it doesn't work. I use MySQL that doesn't support rank()

This forum is for MS SQL Server
Post your question www.mysql.com

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site