asked on

SQL Query Pattern in DB2

Hello
I need a SQL Output as below

Company Name Age
A abc 31
bcd 33
xyz 34
B 123 44
234 55
345 33

Please help

what do you mean by this output?
which version of db2 are you using?
you can try this:

select company1 as company, name, age
from (
select *, case when rown=1 then company else '' end as company1 from (
select company, name, age, rownumber() over(partition by company) as rown
from yourTable ) t1
) t2
order by company,rown

Senz79

ASKER

can you help me with the fields below

DATE ('20' || substr("Timestamp",2,2) || '-' || substr("Timestamp",4,2) || '-' || substr("Timestamp",6,2)) as "Date",
TIME (substr("Timestamp",8,2) || '.' || substr("Timestamp",10,2) || '.' || substr ("Timestamp",12,2)) as "Time",
"Server_Name" from "NT_System_H"

based on the server_name i want to group the result.

Senz79

ASKER

i am using DB2 version 9

momi_sabag

when you want to group by server_name you need to decide which function to apply to every other column such as date - the min / max / avg function?

Senz79

ASKER

I didnt get that ...... In case i want a output pattern i discussed.... how can that help

momi_sabag

can you post here the output you got?

Senz79

ASKER

I wanted a output like

Company Name Age
A abc 31
bcd 33
xyz 34
B 123 44
234 55
345 33

momi_sabag

i know that
but what is the output you get from my statement?

Senz79

ASKER

db2 => select "Server_Name" , "LAT_User_Name" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name" from (se
lect "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Serv
er_Name","rown"

SQL0206N "LAT_User_Name" is not valid in the context where it is used.
SQLSTATE=42703
db2 =>

momi_sabag

try

select "Server_Name" , "LAT_User_Name"
from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name", *
from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LAT_User_Name") as "rown" from "NT_System_H" ) t1) t2
order by "Server_Name","rown"

Senz79

ASKER

db2 => select "Server_Name" , "LAT_User_Name" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name", * from
(select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LAT_User_Name") as "rown" from "NT_Syste
m_H" ) t1) t2 order by "Server_Name","rown"

SQL0104N An unexpected token "*" was found following "nd as "Server_Name",".
Expected tokens may include: "<select_sublist>". SQLSTATE=42601
db2 =>

momi_sabag

select "Server_Name1" , "LAT_User_Name"
from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1", "Server_Name" , "LAT_User_Name", rown
from
(select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LAT_User_Name") as "rown" from "NT_Syste
m_H" ) t1) t2 order by "Server_Name","rown"

Senz79

ASKER

db2 => select "Server_Name1" , "LAT_User_Name" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1", "Serv
er_Name" , "LAT_User_Name", rown from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LA
T_User_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Server_Name","rown"

SQL0206N "ROWN" is not valid in the context where it is used. SQLSTATE=42703
db2 =>

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Senz79

ASKER

still the same error
db2 => select "Server_Name1" , "LAT_User_Name","rown" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1"
, "Server_Name" , "LAT_User_Name", rown from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order
by "LAT_User_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Server_Name","rown"
SQL0206N "ROWN" is not valid in the context where it is used. SQLSTATE=42703
db2 =>

Senz79

ASKER

select "Server_Name1" , "LAT_User_Name","rown" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1", "Server_Name" , "LAT_User_Name", "rown" from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LAT_User_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Server_Name","rown"

GOT IT !!!! THANKS A MILLION :)