How To generate id based on joining query result

Question

I need to listing counting data and based "rate" values joining two tables .

Rate Table

 id    rate    ratevale
 -------------------------    
  1     N        NA    
  2     D        DH    
  3     F        FA    
  4     L        LD

Claim Table

I need list the result following like that

id  Generate

1   NA-40-001

2   NA-40-002

3   NA-40-003

4   FA-40-001

5   NA-40-004

6   DH-40-001

7   DH-40-002

8   FA-40-002

9   LD-40-001

I have worked query following

$query="select count(Claim.rate) as count,Rate.ratevale from Claim leftjoin Rate on Claim.rate=Rate.rate group by Claim.rate";

Its display the count of each "rate and ratevalue" like following

NA->4

FA->2

DH->2

LD->1

But i need to how to list data above format.

What are the `40` and `00x` values in your "Generate" column? — Christof, Jun 09 '15 at 06:01

score 0 · Answer 1 · answered Jun 09 '15 at 06:05

0

Try this Code :

$query="SELECT COUNT( Claim.rate ) AS count, Rate.ratevale
FROM claim
LEFT JOIN rate ON Claim.rate = Rate.rate
GROUP BY Claim.rate" ;

With Order By

$query = "SELECT COUNT( claim.rate ) AS count, rate.ratevale
FROM claim
LEFT JOIN rate ON claim.rate = rate.rate
GROUP BY claim.rate
ORDER BY count DESC" ;

answered Jun 09 '15 at 06:05

Manoj Kushwaha

114
12

I need like "1 NA-40-001 2 NA-40-002 3 NA-40-003 4 FA-40-001 5 NA-40-004 6 DH-40-001 7 DH-40-002 8 FA-40-002 9 LD-40-001" – user2725587 Jun 09 '15 at 07:14
See this link: [http://stackoverflow.com/questions/11861799/mysql-query-to-select-results-with-auto-increment-as-a-new-column-added-in-the-r] – Manoj Kushwaha Jun 09 '15 at 08:32

score 0 · Answer 2 · edited May 23 '17 at 11:43

With the tables

CREATE TABLE ctab (id int,rate varchar(1)); -- claims table
INSERT INTO ctab (id,rate) VALUES
    (1, 'N'),(2, 'N'),(3, 'N'),(4, 'F'),(5, 'N'),
    (6, 'D'),(7, 'D'),(8, 'F'),(9, 'L');

CREATE TABLE rtab(id int,rate varchar(1), ratevale varchar(2));
INSERT INTO rtab (id,rate,ratevale) VALUES  -- rates table
    (1, 'N', 'NA'),(2, 'D', 'DH'),(3, 'F', 'FA'),(4, 'L', 'LD');

we can do the following:

SELECT t.id, CONCAT(ratevale,'-40-',RIGHT(CAST(i+1000 as char(4)),3)) rval
FROM (
 SELECT CASE WHEN @r =rate THEN @i:=@i+1 ELSE @i:= 1 END i,
        CASE WHEN @r!=rate THEN @r:=rate ELSE @r     END r, 
 id id,rate rate 
 FROM (SELECT @i:=1,@r:='') v, ctab c 
 ORDER BY rate,id
) t 
INNER JOIN rtab r ON r.rate=t.rate
ORDER BY id

and we get:

| id |      rval |
|----|-----------|
|  1 | NA-40-001 |
|  2 | NA-40-002 |
|  3 | NA-40-003 |
|  4 | FA-40-001 |
|  5 | NA-40-004 |
|  6 | DH-40-001 |
|  7 | DH-40-002 |
|  8 | FA-40-002 |
|  9 | LD-40-001 |

Play around with it here: http://sqlfiddle.com/#!9/d9e3e/15

The basic idea is that we list the claims table, ordered by rate and turn it into a derived table t.

Having initialised @i to 1 and @r to '' in the derived table v we check whether @r=rate and

if so, we increment @i.
otherwise (ELSE) we reset @i to 1 and @r to rate. This happens in the second CASE clause.

We end up with a sequence column i that starts counting for each value of rate. In the outer SELECT statement the derived table t is then joined to the rates table rtab and a different sorting order is applied.

Please note:

This solution can also be seen as an alternative solution to ROW_NUMBER() in MySQL . The above MySQL query in can easily be rewritten in MSSQL by applying row_number() over (partition...), see here: http://sqlfiddle.com/#!3/d9e3e/8

How To generate id based on joining query result

2 Answers2