SELECT query help - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT query help

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-11-03 : 05:23:27

Hello All

Is there any option in SELECT query to select a particular row of interest ?

for ex: In my application the result set i get is something like

Step Data
-------------
1    ITEM11
1    ITEM12
1    ITEM13
2    ITEM21
2    ITEM22
2    ITEM23

If i have to select ITEM21 based on the index [2].How can i use the SELECT statement in such a case to retrieve the correct data

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 05:30:47

where data like 'ITEM2%'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-03 : 05:44:36

[code]
select top 1 Data
from yourtable
where Step = 2
order by Data
[/code]

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-11-03 : 06:16:10

Hello
I cannot use this query.
TOP(1) always returns the first item i.e ITEM21
what if i needed ITEM22 or ITEM23 ??

quote:
Originally posted by khtan

select top 1 Data
from yourtable
where Step = 2
order by Data

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-11-03 : 06:23:59

Hello Madhivanan
Actually i think i didnt explain the problem properly.
The result i get is during the runtime, and i have no idea what is returned, hence i cannot use such a filter 'ITEM2%'

I have explained my scenario here pictorially.
I have an index say 2, depending on this index whenever i SELECT
from this table with filter set on Column : Step
i should be able to get the result ITEM22


          Step Data
          -------------
          1    ITEM11
          1    ITEM12
          1    ITEM13
          2    ITEM21
    -->   2    ITEM22
          2    ITEM23

quote:
Originally posted by madhivanan

where data like 'ITEM2%'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-03 : 06:38:57

what do you mean by index ? and what is index = 2 ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-03 : 06:39:35

quote:
Originally posted by srivatsahg

Hello
I cannot use this query.
TOP(1) always returns the first item i.e ITEM21
what if i needed ITEM22 or ITEM23 ??

quote:
Originally posted by khtan

select top 1 Data
from yourtable
where Step = 2
order by Data

KH
[spoiler]Time is always against us[/spoiler]

but you never says you need it in your 1st post

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-11-03 : 07:07:53

Hello
Sorry for not explaining my problem correctly

Result i need, if index = 2 and Step = 2

          
          Step Data
          -------------
          1    ITEM11
          1    ITEM12
          1    ITEM13
          2    ITEM21
    -->   2    ITEM22
          2    ITEM23

Result i need, if index = 3 and Step = 1

          
          Step Data
          -------------
          1    ITEM11
          1    ITEM12
       -->1    ITEM13
          2    ITEM21
          2    ITEM22
          2    ITEM23

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:13:08

where step=@step and data like 'ITEM'+@step+'%'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-03 : 07:25:40

[code]
select *
from
(
select *, idx = row_number() over (partition by Step order by Data)
from yourtable
) a
where Step = 1
and idx = 3
[/code]

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-11-03 : 08:19:29

Thanks a ton ..!!
I needed a similar query
I modified your query for my requirement.

quote:
Originally posted by khtan

select *
from
(
select *, idx = row_number() over (partition by Step order by Data)
from yourtable
) a
where Step = 1
and idx = 3

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources