CODEWITHSUNDEEP
sqlmultiple-columns
user1735894
user1735894

Reputation: 323

SQL, get top 2 rows and turn fields into columns

I am trying to complete what to me is a complex query and am a little stuck. I am returning several rows but I only want the first 2 rows and I need to turn the ItemNum column into two columns Item1 & Item2 So I have a result set that looks like this. THe Data source will have 1 - any number of rows for each email and Category.

Email           Category ItemCount  Date                    ItemNum
[email protected] CASE FANS   3       2013-07-16 00:00:00.0   11-999-344
[email protected] CASE FANS   3       2013-07-16 00:00:00.0   35-103-178
[email protected] CASE FANS   3       2013-07-16 00:00:00.0   35-129-060
[email protected] CASE FANS   4       2013-07-16 00:00:00.0   35-146-003
[email protected] CASE FANS   4       2013-07-16 00:00:00.0   35-146-017
[email protected] CASE FANS   4       2013-07-16 00:00:00.0   35-146-017
[email protected] CASE FANS   4       2013-07-16 00:00:00.0   35-146-017

I would love to have the results look like this

Email           Category    Date                    Item1         Item2
[email protected] CASE FANS   2013-07-16 00:00:00.0   11-999-344    35-103-178  
[email protected] CASE FANS   2013-07-16 00:00:00.0   11-999-344    35-103-178

So to add to the frustration, I am not able to use TempTables or Declare any varibales.

Any help would be appreciated.

Thanks

Upvotes: 0

Views: 152

Answers (3)

Luis LL
Luis LL

Reputation: 2993

try this

;WITH Ranked AS 
(
    select  Email, Category, ItemNum, ROW_NUMBER() OVER ( PARTITION BY  Email, Category ORDER BY ItemCount, Date) AS RowNo
    from MyTable
)
, TopFirst AS 
(
    SELECT Email, Category, ItemNum, RowNo
    FROM Ranked
    WHERE RowNo = 1
)
, TopSecond AS 
(
    SELECT Email, Category, ItemNum, RowNo
    FROM Ranked
    WHERE RowNo = 2
)
SELECT TopFirst.Email, TopFirst.Category, TopFirst.ItemNum AS Item1, ISNULL(TopSecond.RowNo, '')
FROM TopFirst
LEFT OUTER JOIN TopSecond ON TopFirst.Email = TopSecond.Email AND  TopFirst.Category  =  TopSecond.Category

Upvotes: 0

William M-B
William M-B

Reputation: 321

Depending on the database you're using, you could try adding the following into your SELECT statement (I'm unable to test against your code, so this will probably need tinkering):

SELECT email OVER(PARTITION BY email, ItemNum ORDER BY email) as EmailPartition

This doesn't solve the issue of adding in columns for each item, but is a viable option for sorting out your email list and selecting a DISTINCT email/ItemNum list. This in combination with Joe Taras' answer should produce an efficient query.

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15399

try this:

select a1.email, a1.itemNum, 
(select distinct a3.itemNum
    from myTable a3
    where a3.email = a1.email
    and a3.itemNum > a1.itemNum
    and not exists(
        select 'x' from myTable a4
        where a4.email = a3.email
        and a4.itemNum > a1.itemNum
        and a4.itemNum < a3.itemNum)
)
from myTable a1
where not exists
(select 'x' from myTable a2
where a2.email = a1.email
and a2.itemNum < a1.itemNum)

I assume your field discrimination is email (the same for several rows) and itemNum.

You can change the criteria about existing condition.

Upvotes: 1

Related Questions