CODEWITHSUNDEEP
sql-server
wasimjee
wasimjee

Reputation: 49

Different Queries Results to one Row

I am writing three different query and want there result as all in single row

example : 

SELECT top 1 processdate as gold FROM goldchart WHERE instrument = 'gold'
   order by processdate desc

SELECT top 1 processdate as silver FROM silverchart WHERE instrument = 'silver'
   order by processdate desc

SELECT top 1 processdate as usoil FROM usoilchart WHERE instrument = 'usoil'
   order by processdate desc

The result i am getting is : 

gold
2014-04-10
silver
2014-01-11
usoil
2014-02-14

The result i need is :

gold        silver       usoil 
2014-04-10  2014-01-11   2014-02-14

Need to Combine all the three queries and get the result as one single row.

Upvotes: 1

Views: 36

Answers (3)

DanW
DanW

Reputation: 247

this works, at least on SQLServer.

SELECT TOP 1 gc.processdate AS gold, sc.processdate AS silver, uc.processdate AS usoil 

FROM goldchart gc, silverchart sc, usoilchart uc 

ORDER BY gc.processdate DESC, sc.processdate DESC, uc.processdate DESC

Upvotes: 0

konkked
konkked

Reputation: 3231

You can just wrap the select statements in parenthesis and group them together

SELECT 
 (SELECT top 1 processdate FROM goldchart WHERE instrument = 'gold'
  order by processdate desc) as gold,

 (SELECT top 1 processdate FROM silverchart WHERE instrument = 'silver'
  order by processdate desc) as silver,

(SELECT top 1 processdate FROM usoilchart WHERE instrument = 'usoil'
  order by processdate desc) as usoil

Upvotes: 1

xQbert
xQbert

Reputation: 35323

I think SQL server allows you do do this w/o a from...

Select 
(SELECT top 1 processdate FROM goldchart WHERE instrument = 'gold' order by processdate desc) as gold, 
(SELECT top 1 processdate FROM silverchart WHERE instrument = 'silver' order by processdate desc) as silver, 
(SELECT top 1 processdate FROM usoilchart WHERE instrument = 'usoil' order by processdate desc) as usoil

Upvotes: 1

Related Questions