Luke101 Luke101 - 6 months ago 11
SQL Question

How to group by a column

Hi I know how to use the group by clause for sql. I am not sure how to explain this so Ill draw some charts. Here is my original data:

Name Location
----------------------
user1 1
user1 9
user1 3
user2 1
user2 10
user3 97


Here is the output I need

Name Location
----------------------
user1 1
9
3
user2 1
10
user3 97


Is this even possible?

Answer

Hope this helps


SELECT A.FINAL_NAME, A.LOCATION
  FROM (SELECT DISTINCT DECODE((LAG(YT.NAME, 1) OVER(ORDER BY YT.NAME)),
                               YT.NAME,
                               NULL,
                               YT.NAME) AS FINAL_NAME,
                        YT.NAME,
                        YT.LOCATION
          FROM YOUR_TABLE_7 YT) A

As Jirka correctly pointed out, I was using the Outer select, distinct and raw Name unnecessarily. My mistake was that as I used DISTINCT , I got the resulted sorted like


1           1
2   user2   1
3   user3   97
4   user1   1
5           3
6           9
7          10

I wanted to avoid output like this.

Hence I added the raw id and outer select

However , removing the DISTINCT solves the problem. Hence only this much is enough


SELECT DECODE((LAG(YT.NAME, 1) OVER(ORDER BY YT.NAME)),
              YT.NAME,
              NULL,
              YT.NAME) AS FINAL_NAME,
       YT.LOCATION
  FROM SO_BUFFER_TABLE_7 YT

Thanks Jirka