Return 3 rows as one - SQL Server Forums

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

SQL Server Development (2000)

Return 3 rows as one

Author

Topic

bsalta
Starting Member

1 Post

Posted - 2005-08-17 : 12:15:00

I have encountered a problem, I know just enough SQL to get me by with basic commands. However, I cant get by this one.

I am trying to select the top 3 records and return them as one row.
Example:

SELECT TOP 3 * FROM productlistings WHERE ProductID = 100001 ORDER BY MaxBidPPC DESC

This select statement above is selecting one product and the top 3 bids for the product in descending order.

I would then need to place these three values into a table and return that row...

Example Table to be returned:

ColumnName: TopBid1 TopBid2 TopBid3
Value: 0.75 0.65 0.64

Those three values (0.75, 0.65, 0.64) were returned from the top SQL statement.

Any help would be appreciated!

Thanks

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 12:30:10

Probably horribly inefficient but this might work:


SELECT TopBid1, TopBid2, TopBid3
FROM
(
    SELECT TOP 1 Value as TopBid1
    FROM productlistings 
    WHERE ProductID = 100001 
    ORDER BY MaxBidPPC DESC
) Top1,
(
    SELECT TOP 1 Value as TopBid2
    FROM
    (
        SELECT TOP 2 Value
        FROM productlistings 
        WHERE ProductID = 100001 
        ORDER BY MaxBidPPC DESC
    ) X2
    ORDER BY MaxBidPPC ASC
) Top2,
(
    SELECT TOP 1 Value as TopBid3
    FROM
    (
        SELECT TOP 3 Value
        FROM productlistings 
        WHERE ProductID = 100001 
        ORDER BY MaxBidPPC DESC
    ) X3
    ORDER BY MaxBidPPC ASC
) Top3

Kristen

Sprinjee
Starting Member

42 Posts

Posted - 2005-08-17 : 12:31:00

So you want the result to be: 0.750.650.64

this is easier perhaps

select top 3 cast(value 1 as varchar) + cast(value 2 as varchar) + cast(value 3 as varchar) from Table ORDER BY MaxBidPPC DESC

with spaces:

select top 3 cast(value 1 as varchar) + ' ' + cast(value 2 as varchar) + ' ' + cast(value 3 as varchar) from Table ORDER BY MaxBidPPC DESC

Never mind see that its about rows not cols

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources