Help in SQL Query required

Question

3.86/5 (4 votes)

See more:

I have the following dataset

type id	value
1       a
1	b
2	c
2	d
3	e
3	f
1	g
3	h
2	i

I want to write a query to display the following result

type id	value
1	a
2	c
3	e
1	b
2	d
3	f
1	g
2	i
3	h

Posted 20-Dec-12 12:59pm

Hasham Ahmad

Updated 20-Dec-12 13:10pm

chaau

v2

Add a Solution

Comments

Christian Graus 20-Dec-12 19:59pm

I'm not sure this is possible without doing it in code. It's a reasonably complex rule, I think. The numbers only matter in each group, they don't have an absolute worth. You also assume that there's no pair that has duplicate letters.

chaau 20-Dec-12 20:22pm

It is possible now. With the help of OVER PARTITION BY clause.I had to learn it myself just recently for a project I was working on.

chaau 20-Dec-12 20:05pm

for some stupid reason I can't submit the solution.

select *
from dataset
order by row_number() over (partition by type_id order by value), type_id

SQL Fiddle:
http://sqlfiddle.com/#!3/0004d/17

chaau 20-Dec-12 20:11pm

looks like I could finally post my solution. codeproject is slow today for some reason

Christian Graus 21-Dec-12 16:11pm

Thanks - that is awesome. That's my homework over xmas.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chaau · Accepted Answer · 2012-12-20T14:05:00

Solution 2

SQL

select *
from dataset
order by row_number() over (partition by type_id order by value), type_id

Please check the SQL Fiddle[^]

Posted 20-Dec-12 14:05pm

chaau

Comments

[no name] 20-Dec-12 20:12pm

it worked, thanks a lot man :)

chaau 20-Dec-12 20:23pm

BTW, it is a very good interview question. I recommend you learn more about this OVER PARTITION BY clause

[no name] 21-Dec-12 10:39am

I had been working with rownumber for long before posting this here. Learnt the real meaning of partition by :) practicing now with different dataset :)

__TR__ 21-Dec-12 5:30am

My 5!

Help in SQL Query required

1 solution

Solution 2

Add your solution here

Preview 0

Help in SQL Query required

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us