Get require data from the table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Get require data from the table

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-08 : 15:33:57

Hello,

I have a table that is


Descr    ID             AMT          TIME                EMP    RGS
         TOTAL
Cash	000000052037	5.00	2009-01-08 09:25:26.337	3111	1	400.00
Cash	000000052037	95.00	2009-01-08 09:25:33.377	3111	1	400.00
Cash	000000052038	8.88	2009-01-08 09:37:53.070	3111	1	400.00
Cash	000000052038	91.12	2009-01-08 09:37:55.823	3111	1	400.00
Cash	000000052039	7.77	2009-01-08 10:51:41.467	3111	1	400.00
Cash	000000052039	92.23	2009-01-08 10:51:44.250	3111	1	400.00
Cash	000000052040	5.00	2009-01-08 14:51:15.537	3111	1	400.00
Check	000000052040	4.00	2009-01-08 14:51:15.557	3111	1	400.00
Cash	000000052040	95.00	2009-01-08 14:51:18.390	3111	1	400.00
Check	000000052040	-4.00	2009-01-08 14:51:18.420	3111	1	400.00

What I want is

Cash	000000052037	5.00	2009-01-08 09:25:26.337	3111	1	400.00
Cash	000000052038	8.88	2009-01-08 09:37:53.070	3111	1	400.00
Cash	000000052039	7.77	2009-01-08 10:51:41.467	3111	1	400.00
Cash	000000052040	5.00	2009-01-08 14:51:15.537	3111	1	400.00
Check	000000052040	4.00	2009-01-08 14:51:15.557	3111	1	400.00
Check	000000052040	-4.00	2009-01-08 14:51:18.420	3111	1	400.00

What is the proper query?

Thanks

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 15:39:16

on what basis are these in your output
5.00
8.88
7.77
5.00
4.00
-4.00

and these are not
95.00
91.12
92.23
95.00

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-08 : 15:45:49

For the Cash, I want to get the first row from those have same ID.
For Non cash, I may want all.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 15:51:54

something like this might work for you



SELECT DESCR,
       ID_TOTAL,
       AMT,
       TIME,
       EMP,
       RGS
FROM   (SELECT DESCR,
               ID_TOTAL,
               AMT,
               TIME,
               EMP,
               RGS,
               ROW_NUMBER()
                 OVER(PARTITION BY ID_TOTAL ORDER BY TIME ASC) AS SEQ
        FROM   MY_TABLE) A
WHERE  A.SEQ = 1
        OR A.DESCR = 'Check'

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-08 : 15:54:28

Suppose this table is called table1.
What is the query like?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 15:55:46

quote:

Originally posted by rohitkumar

something like this might work for you



SELECT DESCR,
       ID_TOTAL,
       AMT,
       TIME,
       EMP,
       RGS
FROM   (SELECT DESCR,
               ID_TOTAL,
               AMT,
               TIME,
               EMP,
               RGS,
               ROW_NUMBER()
                 OVER(PARTITION BY ID_TOTAL ORDER BY TIME ASC) AS SEQ
        FROM   MY_TABLE) A
WHERE  A.SEQ = 1
        OR A.DESCR = 'Check'

above is wrong, try this

SELECT DESCR,
       ID_TOTAL,
       AMT,
       TIME,
       EMP,
       RGS
FROM   (SELECT DESCR,
               ID_TOTAL,
               AMT,
               TIME,
               EMP,
               RGS,
               ROW_NUMBER()
                 OVER(PARTITION BY ID_TOTAL ORDER BY TIME ASC) AS SEQ
        FROM   MY_TABLE
		WHERE DESCR = 'Cash') A
WHERE  A.SEQ = 1
UNION ALL
SELECT DESCR,
       ID_TOTAL,
       AMT,
       TIME,
       EMP,
       RGS
FROM   MY_TABLE
WHERE DESCR = 'Check'

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 15:57:03

replace MY_TABLE (2 places) in the query with your table name

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-08 : 16:08:06

Can we use having(count(*) %2) something like?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 16:10:20

quote:
Originally posted by zhshqzyc

Can we use having(count(*) %2) something like?

why do you want to use HAVING?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-08 : 16:11:52

to get the first row.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 16:18:20

cant think of a way of using having(count(*)%2) to get the desired result.

I was asking the reason why do you want to use HAVING to get the first row? your teacher/boss asked you?

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-08 : 20:01:17

Rohit suggested the correct way. If you HAVE to use count, try this
Select Descr, IDTotal, max(Amt), Max(Time), Max(Emp), Max(RGS) from #x
Group by IDTotal, Descr having count(IDTOtal) > 1
UNION
Select Descr, IDTotal, max(Amt), Max(Time), Max(Emp), Max(RGS) from #x
Group by IDTotal, Descr

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-08 : 20:02:31

Here is a cleaner version with your table name

Select
Descr,
IDTotal,
max(Amt),
Max(Time),
Max(Emp),
Max(RGS)
from
Table1
Group by
IDTotal,
Descr
having
count(IDTOtal) > 1
UNION
Select
Descr,
IDTotal,
max(Amt),
Max(Time),
Max(Emp),
Max(RGS)
from
Table1
Group by
IDTotal,
Descr

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-09 : 01:00:17

May Be Like This,,,,

with ashishashish as
(select *,row_number() over(partition by descr order by descr)as RN from (select idtotal,descr,max(amt) as AMT,max(time) as TIME,max(emp) as EMP,max(rgs) as RGS from test16 group by idtotal,descr) A)
select * from ashishashish where descr='cash'
union ALL
select B.IDTOTAL,B.DESCR,B.AMT,B.TIME,B.EMP,B.RGS,row_number() over(order by descr)AS rn from test16 B WHERE B.DESCR='check'

Thanks,,,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources