Get different row record in 1 row

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 2000 Forums

Transact-SQL (2000)

Get different row record in 1 row

Author

Topic

nigege20
Starting Member

4 Posts

Posted - 2009-06-22 : 04:49:25

as title: Get different row record in 1 row

date data1 data2
1/1/2009 10 15
1/2/2009 5 9
1/3/2009 8 8

i would like to get the record like this

data1Max data1Min data2Max data2Min
8 10 8 15

data1max means the last day data1
data1Min means the 1st day data1

is it possible to retrieve this records in 1 sql query?
is urgent, please help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 05:00:22

[code]SELECT MAX(CASE WHEN theOrder = 1 THEN data1 ELSE NULL END) AS data1Max,
MAX(CASE WHEN theOrder = 0 THEN data1 ELSE NULL END) AS data1Min,
MAX(CASE WHEN theOrder = 1 THEN data2 ELSE NULL END) AS data2Max,
MAX(CASE WHEN theOrder = 0 THEN data2 ELSE NULL END) AS data2Min
FROM (
SELECT Data1,
Data2,
theOrder
FROM (
SELECT TOP 1 Data1,
Data2,
0 AS theOrder
FROM @Sample
ORDER BY [Date]
) AS d

UNION ALL

SELECT Data1,
Data2,
theOrder
FROM (
SELECT TOP 1 Data1,
Data2,
1 AS theOrder
FROM @Sample
ORDER BY [Date] DESC
) AS d
) AS q[/code]

E 12°55'05.63"
N 56°04'39.26"

nigege20
Starting Member

4 Posts

Posted - 2009-06-22 : 05:19:53

thanks for reply, if i want to use this query in VB6, then how do i convert it?
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 05:23:32

rs.MoveFirst

Data2Min = rs("Data1").Value
Data2Max = rs("Data2").Value

rs.MoveLast

Data1Min = rs("Data1").Value
Data1Max = rs("Data2").Value

E 12°55'05.63"
N 56°04'39.26"

nigege20
Starting Member

4 Posts

Posted - 2009-06-23 : 23:05:50

i have find out the sql query

Select T1.data1 as Data1Max, T1data2 as Data2Max ,T2.data1 as data1Min, T2.data2 as data2Min from
(select Data1, Data2 from tbl1 order by date desc) T1, (select data1 as data1Min,data2 as data2Min from tbl1 order by date asc) T2

result:
Data1Max Data2Max Data1Min Data2Min
8 8 10 15

thanks Peso give me idea also

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 00:36:34

Where are the TOP 1 in the derived tables?

E 12°55'05.63"
N 56°04'39.26"

nigege20
Starting Member

4 Posts

Posted - 2009-07-01 : 20:01:20

since i have sort the data by date desc, so 1st record will be the last date data. then top 1 no need lo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources