CODEWITHSUNDEEP
sql-servert-sql
bircastri
bircastri

Reputation: 2165

How to count every row from select

I want to extract the data from my table and I want to have this result:

1 Valore
2 Valore
3 Valore
4 Valore

So I want to numeration every ROW from 1 to N.

This is my query but not works:

  SELECT COUNT(DISTINCT dbo.F_ElementStringFromCodeDescription(RR.ID, RR.DisplayName)) AS Count, 
         dbo.F_ElementStringFromCodeDescription(RR.ID, RR.DisplayName) AS NAME
    FROM AA_V_PHR_CCD_ResultsObservation R INNER JOIN 
         AA_V_PHR_CCD_ResultsObservationXLanguages RR   ON R.ID = RR.ID
   WHERE CodeSystem = '123456789'
GROUP BY dbo.F_ElementStringFromCodeDescription(RR.ID, RR.DisplayName)

EDIT

My Database is Tsql

and this is my extract data:

1 Valore
1 Pippo
1 Pluto

Instead of

1 Valore
2 Pippo
3 Pluto

Upvotes: 1

Views: 110

Answers (4)

Ricketyship
Ricketyship

Reputation: 654

You could do using the rownum function. This would be specific to each DB you are using, but here's an example from oracle:

Select rownum, <ColumnList> from 
       (select <ColumnList> ...)

The TSQL documentation says that the equivalent is ROW_NUMBER() function.

For TSQL:

Select ROW_NUMBER() OVER
   ([PartitionBy] OrderByClause), <ColumnList>...

Upvotes: 1

Neo
Neo

Reputation: 135

This should do the job:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Count, 
     dbo.F_ElementStringFromCodeDescription(RR.ID, RR.DisplayName) AS NAME
FROM AA_V_PHR_CCD_ResultsObservation R INNER JOIN 
     AA_V_PHR_CCD_ResultsObservationXLanguages RR   ON R.ID = RR.ID
WHERE CodeSystem = '123456789'
GROUP BY dbo.F_ElementStringFromCodeDescription(RR.ID, RR.DisplayName)

Upvotes: 1

Gianfrancesco Aurecchia
Gianfrancesco Aurecchia

Reputation: 780

This is my solution is the fastest

CREATE TABLE Test (
  ID INT PRIMARY KEY,
  VAL VARCHAR(64));

INSERT INTO Test (ID,VAL) VALUES (8,"a"),(156,"b"),(99,"c");

SET @t1=0;
SELECT @t1:=@t1 + 1 as 'num', VAL FROM Test;

OUT 

num | VAL
1   | a
2   | c
3   | b

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062600

The count does not change per-row; there are the same number of rows every time; what you seem to want is not a count, but a row-number.

To get a row-number, you need to use the ROW_NUMBER API; for example:

SELECT ROW_NUMBER() OVER (
   ORDER BY dbo.F_ElementStringFromCodeDescription(RR.ID, RR.DisplayName)) as [Position],
dbo.F_ElementStringFromCodeDescription(RR.ID, RR.DisplayName) as [Name]
...
ORDER BY dbo.F_ElementStringFromCodeDescription(RR.ID, RR.DisplayName)

However, unless you are actually partitioning (so getting a row-number per partition), it would be easier to handle this at the receiving client.

Upvotes: 2

Related Questions