SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get the position of a value?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gabriel.k
Starting Member

6 Posts

Posted - 04/11/2007 :  17:00:58  Show Profile  Reply with Quote
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

Sweden
30108 Posts

Posted - 04/11/2007 :  17:38:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gabriel.k
Starting Member

6 Posts

Posted - 04/12/2007 :  14:13:12  Show Profile  Reply with Quote
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)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/12/2007 :  14:19:53  Show Profile  Reply with Quote
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



Go to Top of Page

gabriel.k
Starting Member

6 Posts

Posted - 04/12/2007 :  15:27:36  Show Profile  Reply with Quote
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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 04/12/2007 :  15:38:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 04/12/2007 15:40:01
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000