How to get the position of a value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the position of a value?

Author

Topic

gabriel.k
Starting Member

6 Posts

Posted - 2007-04-11 : 17:00:58

Hi,
I'm trying to get the position of a single value in the query results.
like this:


select * from tPerson order by ds_alias

results in

id_person | ds_name | ds_alias
    15    |  mark   |    AA
    20    |  john   |    AA
    5     |  mike   |    BB
    8     | thomas  |    JK
    2     |  mike   |    MM

as you can see, I can have registries with the same name and with the same alias, there's no restriction

and I'm trying to find out how many registries there are before the second "mike", wich would be 3.. so it's the 4th element, get it?

I have no idea how to...
Thank in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 17:38:08

[code]-- Prepare sample data
DECLARE @Sample TABLE (id_person INT, ds_name VARCHAR(10), ds_alias VARCHAR(10))

INSERT @Sample
SELECT 15, 'mark', 'AA' union all
SELECT 20, 'john', 'AA' union all
SELECT 5, 'mike', 'BB' union all
SELECT 8, 'thomas', 'JK' union all
SELECT 2, 'mike', 'MM'

-- Stage some data
DECLARE @Name VARCHAR(20)

SELECT @Name = ds_name
FROM @Sample
WHERE ds_alias = 'mm'

-- Show the expected output
SELECT COUNT(*) + 1 AS Position
FROM @Sample
WHERE ds_alias <= 'mm'
AND ds_name <> @Name[/code]

Peter Larsson
Helsingborg, Sweden

gabriel.k
Starting Member

6 Posts

Posted - 2007-04-12 : 14:13:12

I had tried using count, but it didn't work...
Two registries can have the same alias, so how can I make the count stop at the element I want so I can get it's position?

Heres an example with some real data (values are in portuguese)


SELECT	 *
FROM	 clients
WHERE	 ds_name <= 'a quatro'
	 and ds_alias <> ''
ORDER BY ds_alias
---------------------------------
cd_client  |  ds_name				 	 | ds_alias
26	   |  BANCO SANTOS NEVES		 	 | BCO STO NEVES
1857	   |  GIOVANNA GONÇALVES DIAS	 	 	 | 100% EVENTOS
1973	   |  VITÓRIA ENTRETENIMENTO LTDA	 	 | 100% VIDEO
240	   |  3 PONTOS PROPAGANDA LTDA		 	 | 3 PONTOS PROPAGANDA
2	   |  A EDICAO LIVRARIA E DIST. LTDA	 	 | A EDICAO LIVRARIA
538	   |  S/A A GAZETA			 	 | A GAZETA (JORNAL)
3	   |  A GAZETA DO ESPIRITO SANTO RADIO E TV LTDA | A GAZETA (RADIO / TV)
340	   |  A4 PUBLICIDADE E MARKETING LTDA		 | A QUATRO
2463	   |  SDC/QUATRO COMUNICAÇÃO E MARKETING LTDA	 | A QUATRO

There are two clients with the 'A QUATRO' alias, the problem is that if I do a count of all elements with an alias <= 'A QUATRO' it'll return 9.
How can I discriminate wich one I want, so I can get it's position?

(sorry for the really awful english)

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-12 : 14:19:53

The order of data in the database has no meaning. Unless you have that relates the rows to each other, you may be able to force a hack, but it won't be true in all cases.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

gabriel.k
Starting Member

6 Posts

Posted - 2007-04-12 : 15:27:36

I did it! Yay

It's ugly as sin, though....

I realised that I was trying to get the number of elements with a "smaller" alias plus all the elements with the same alias but with smaler id's (since the id is the secondary ordering value), right?
So


select	cd_client (this is the id), ds_name, ds_alias,
	(select (count(c2.cd_client) +
		(select	count(c3.cd_client)
		from	clients c3
		where	c3.ds_alias <> ''
			and c3.ds_alias = c1.ds_alias
			and c3.cd_client <= c1.cd_client))
	from clients c2
	where c2.ds_alias <> ''
	and c2.ds_alias < c1.ds_alias) as Pos
from clients c1
order by c1.ds_name

So i get the code, name and alias ordered by name, but with the position of each element in a result ordered by alias....

It is incredbly ugly... but I couldn't find a way to make a conditional WHERE, so i could compare id's only if the elements had equal aliases...

is there a way this can be done?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 15:38:25

What you are trying to achieve do not match the original description!

Anyhow... Try this

SELECT		s1.cd_client,
		s1.ds_name,
		s1.ds_alias,
		(SELECT COUNT(*) FROM @Sample AS s2 WHERE s2.ds_alias < s1.ds_alias) + 1 +
		(SELECT COUNT(*) FROM @Sample AS s3 WHERE s3.ds_alias = s1.ds_alias AND s3.cd_client < s1.cd_client) AS Pos
FROM		@Sample AS s1
ORDER BY	4

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources