CODEWITHSUNDEEP
sqloracle-database
googly
googly

Reputation: 9

SQL query for group by

I have the table values like below,

seqno,bill_no,bill_seq,first_name,last_name,phone_number,inserted date
1   11  1   KUTH    MUT1    96290   11-OCT-16
2   12  2   KUTH    MUT1    2   11-OCT-16
3   13  3   KUTH    MUT1    3   11-OCT-16
4   14  4   KUTH    MUT1    4   11-OCT-16
5   15  5   KUTH    MUT1    5   11-OCT-16
6   16  6   KUTH    MUT1    6   13-OCT-16
7   17  7   KUTH    MUT1    96290   13-OCT-16
8   18  8   KUTH    MUT1    8   13-OCT-16
9   19  9   KUTH    MUT1    9   13-OCT-16
10  20  10  KUTH    MUT1    96290   13-OCT-16

expected output:

  1. based on phone number it should group
  2. it should sort based on min seq_number and phone number group combination.
  3. The old date rows always come first for example the seqno 1 is inserted on 11-oct but 7 and 10 are inserted on 13-oct but the result start from minimum seqno and old date for a group of phone and for a seqno 2 it should look for any new date row phone number is matching or not, if not matching then sequence 2.. it will continue for other rows as well.
1   11  1   KUTH    MUT1    96290   11-OCT-16
7   17  7   KUTH    MUT1    96290   13-OCT-16
10  20  10  KUTH    MUT1    96290   13-OCT-16
2   12  2   KUTH    MUT1    2   11-OCT-16
3   13  3   KUTH    MUT1    3   11-OCT-16
4   14  4   KUTH    MUT1    4   11-OCT-16
5   15  5   KUTH    MUT1    5   11-OCT-16
6   16  6   KUTH    MUT1    6   13-OCT-16
8   18  8   KUTH    MUT1    8   13-OCT-16
9   19  9   KUTH    MUT1    9   13-OCT-16

Upvotes: 1

Views: 80

Answers (3)

Sebz
Sebz

Reputation: 502

Although it is not quite clear if you are looking for 2 queries or 1, but I think this is what you are looking for:

select 
    seqno, bill_no, bill_seq, first_name, last_name, phone_number, inserted date 
from 
    table
group by
    phone_number, seqno, bill_no, bill_seq, first_name, last_name, inserted date
order by 
    phone_number, seqno asc

Upvotes: 0

Alexander
Alexander

Reputation: 33

Do you want to order first all row by minimum value of seq_number on each phone number group and afterwards by inserted_date within each group?

Then i guess this will do it:

SELECT seqno,bill_no,bill_seq,first_name,last_name,phone_number,inserted_date FROM ( SELECT seqno,bill_no,bill_seq,first_name,last_name,phone_number,inserted_date , MIN (seqno) OVER (PARTITION BY phone_number) AS minimum FROM table1) X ORDER BY minimum,inserted_date

Upvotes: 0

Twinkles
Twinkles

Reputation: 1994

SELECT seqno,bill_no,bill_seq,first_name,last_name,phone_number,inserted date
FROM (
  SELECT seqno,bill_no,bill_seq,first_name,last_name,phone_number,inserted date
    , ROW_NUMBER () OVER (PARTITION BY phone_number ORDER BY seqno) AS rank
  FROM table)
ORDER BY phone_number,rank

Upvotes: 1

Related Questions