469,927 Members | 1,855 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Urgent Query

Hi...

I have a table with the below records and I need to group by the name
and order by the Code

Code Name
----- ----------
P 1 dddd
F 1 ffff
A 1 wwww
I 2 AAA
R 1 ffff
Z 2 AAA
Result should be such that Code with A should be sorted in the Group
with 1 and so on
Name Code
----------- -----
1 wwww A
1 ffff F
1 dddd P
1 ffff R
2 AAA I
2 AAA Z

I have written this query but it gives me the below output :

SELECT Name,Code FROM (SELECT ROW_NUMBER() OVER(PARTITION BY
Name,Code) AS rn, Name,Code FROM tablename ) AS T WHERE rn = 1 order BY
Name
Name Code
----------- -----
1 dddd P
1 ffff F
1 ffff R
1 wwww A
2 AAA I
2 AAA Z

I have tried all combintaions but still am not able to group by the
Code

Thanks.....
RaiNDeEr

Dec 5 '06 #1
3 1935
rAinDeEr wrote:
Hi...

I have a table with the below records and I need to group by the name
and order by the Code

Code Name
----- ----------
P 1 dddd
F 1 ffff
A 1 wwww
I 2 AAA
R 1 ffff
Z 2 AAA
Result should be such that Code with A should be sorted in the Group
with 1 and so on
Name Code
----------- -----
1 wwww A
1 ffff F
1 dddd P
1 ffff R
2 AAA I
2 AAA Z

I have written this query but it gives me the below output :

SELECT Name,Code FROM (SELECT ROW_NUMBER() OVER(PARTITION BY
Name,Code) AS rn, Name,Code FROM tablename ) AS T WHERE rn = 1 order BY
Name
Name Code
----------- -----
1 dddd P
1 ffff F
1 ffff R
1 wwww A
2 AAA I
2 AAA Z

I have tried all combintaions but still am not able to group by the
Code
If I got this right, then you _do not_ want to do any grouping, i.e. you
don't want to use GROUP BY. What you do want to have, however, is a
certain order of the result rows, correct?

SELECT name, code
FROM tablename
ORDER BY INTEGER(SUBSTR(name, 1, POSSTR(name, ' ') - 1)), code

I've been using just the first characters in your NAME column to extract the
number. We sort by this number and then by CODE.

p.s: Ideally, you would use a proper design and normalize your NAME column
into two separate columns.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 5 '06 #2
Hi Knut Stolze ,

thanks a lot !!! i wanted to group by and then do an Order by..

the table has been already designed and we are doing support for the
existing tables where in have to change the queries according to the
requirement...

will look into your Code and i used Locate earlier and now u have given
me another idea..

thanks !!!!
tariq

Dec 5 '06 #3
rAinDeEr wrote:
Hi Knut Stolze ,

thanks a lot !!! i wanted to group by and then do an Order by..
I'm wondering: What exactly are you grouping? Your query doesn't group
anything at all...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 5 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by gopim | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.