getting second row of the table

Hi Everyone,

I want to get the second row of the table. is it possible to get that through a query. For example I have a table

Table A

col1 col2 col3

west east North
hat get mouse

I only want the second row of the table. i don't want the first row. I don't want any other rows. Please let me know if it is possible to do that and how.

Thanks.

You will need to define what you mean by "second row". SQL Server does not store records sequentially, so "second row" really has no meaning unless you specify an ORDER BY in your query.sql

Getting second row in a data source

Hi,

I'm generating a report where 3 photos can be shown on a report if there are 3 photos in our data source.

they are layed out with the main photo on the left, and the other 2 photos on top of each other on the right.

The dataset that is returned from the query has a column called PhotoURL and each photo is a row of the returned dataset

I have inserted three images on the report

The first image I set the value to be =First(Fields!PhotoURL.Value, "Image")

How do you get the second image to have a value of the second row of the dataset, I mean there is no Second function, or movenext that I can see.

The same applies with the third image.

Thanks in advance

Auschucky

Hello Auschucky,

The First function is an aggregation function, which is not suitable for your images-issue.

When the dataset is returning a number of rows, you have to use a list or table on the report to get all rows (images) on the report. However, this doesn't match your report design.

If the dataset always contains one, two or three images, I suggest to change the select statement to return one row with three url fields. You can attach these fields to your images on the report.

Getting results from 2 tables, determined by a 3rd

Hi
Suppose I have 2 tables that each includes 1 column of data and 1 column of
some ID, like this
First table
ID FName
1 John
2 Martin
3 Jenny
Second table
ID SName
A King
B Brown
C Andersson
And then a 3rd table that joins these 2 tables together, like this
Third table
ID1 ID2
1 C
2 A
2 C
3 A
3 B
How would I create a query that effectively lists the third table, but
instead of outputting rows with 1 C and 2 A it should give me John Andersson
and Martin King i.e. giving me the relations from table 3 but using the data
from tables 1 and 2.
I am playing with JOIN at the moment but can't seem to make it join twice in
one query (table1.ID with table3.ID1 and table2.ID with table3.ID2). Hope
I've made myself clear
Thanks for any input
Iblb
Try this
create table #t1(id int, FName varchar(10))
create table #t2(id varchar(1), SName varchar(10))
create table #t3(id int,id2 varchar(1))
insert into #t1 values(1,'John')
insert into #t1 values(2,'Martin')
insert into #t1 values(3,'Jenny')
insert into #t2 values('A','King')
insert into #t2 values('B','Brown')
insert into #t2 values('C','Andersson')
insert into #t3 values(1,'C')
insert into #t3 values(2,'A')
insert into #t3 values(2,'C')
insert into #t3 values(3,'A')
insert into #t3 values(3,'B')
select distinct f.fname + ' ' + l.sname
from #t3 t4 inner join
(select t1.id,t1.fname
from #t3 tt3
inner join #t1 t1 on t1.id=tt3.id
) f on f.id=t4.id
inner join
(select t2.id,t2.sname
from #t3 tt3
inner join #t2 t2 on t2.id=tt3.id2
)l on t4.id2=l.id
VT
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:On%23vszPJHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Hi
> Suppose I have 2 tables that each includes 1 column of data and 1 column
> of some ID, like this
> First table
> ID FName
> 1 John
> 2 Martin
> 3 Jenny
> Second table
> ID SName
> A King
> B Brown
> C Andersson
> And then a 3rd table that joins these 2 tables together, like this
> Third table
> ID1 ID2
> 1 C
> 2 A
> 2 C
> 3 A
> 3 B
> How would I create a query that effectively lists the third table, but
> instead of outputting rows with 1 C and 2 A it should give me John
> Andersson and Martin King i.e. giving me the relations from table 3 but
> using the data from tables 1 and 2.
> I am playing with JOIN at the moment but can't seem to make it join twice
> in one query (table1.ID with table3.ID1 and table2.ID with table3.ID2).
> Hope I've made myself clear
> Thanks for any input
> Ib
>|||Thanks alot your code worked fine.
It was actually a little too fine since I was not aiming at getting the data
from table 1 and 2 edited into 1 column. I want the relations like they are
in table 3 i.e. two rows but instead of outputting two rows of IDs I want to
output two rows containing the corresponding data from table 1 and 2.
Just like you did actually, but just in two rows instead of one, would that
be easy to fix?
I'll save your concatenating code though, I have another task where I think
I can put it to use :)
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:O98n5JQJHHA.4848@.TK2MSFTNGP04.phx.gbl...
> lb
> Try this
>
> create table #t1(id int, FName varchar(10))
> create table #t2(id varchar(1), SName varchar(10))
> create table #t3(id int,id2 varchar(1))
>
> insert into #t1 values(1,'John')
> insert into #t1 values(2,'Martin')
> insert into #t1 values(3,'Jenny')
>
> insert into #t2 values('A','King')
> insert into #t2 values('B','Brown')
> insert into #t2 values('C','Andersson')
> insert into #t3 values(1,'C')
> insert into #t3 values(2,'A')
> insert into #t3 values(2,'C')
> insert into #t3 values(3,'A')
> insert into #t3 values(3,'B')
>
> select distinct f.fname + ' ' + l.sname
> from #t3 t4 inner join
> (select t1.id,t1.fname
> from #t3 tt3
> inner join #t1 t1 on t1.id=tt3.id
> ) f on f.id=t4.id
> inner join
> (select t2.id,t2.sname
> from #t3 tt3
> inner join #t2 t2 on t2.id=tt3.id2
> )l on t4.id2=l.id
>
> VT
>
> "Ib Schrader" <ibschrader@.gmail.com> wrote in message
> news:On%23vszPJHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Suppose I have 2 tables that each includes 1 column of data and 1 column
>> of some ID, like this
>> First table
>> ID FName
>> 1 John
>> 2 Martin
>> 3 Jenny
>> Second table
>> ID SName
>> A King
>> B Brown
>> C Andersson
>> And then a 3rd table that joins these 2 tables together, like this
>> Third table
>> ID1 ID2
>> 1 C
>> 2 A
>> 2 C
>> 3 A
>> 3 B
>> How would I create a query that effectively lists the third table, but
>> instead of outputting rows with 1 C and 2 A it should give me John
>> Andersson and Martin King i.e. giving me the relations from table 3 but
>> using the data from tables 1 and 2.
>> I am playing with JOIN at the moment but can't seem to make it join twice
>> in one query (table1.ID with table3.ID1 and table2.ID with table3.ID2).
>> Hope I've made myself clear
>> Thanks for any input
>> Ib
>

Getting records output from stored procedure into a table

I have a stored procedure that returns two sets of records, and I want to
execute the procedure and insert the second set of records into a temporary
table outside the procedure.
The last lines of the stored procedure look like this:
--Return count of programs first
SELECT COUNT(*)
FROM #Programs
--Return programs
SELECT *
FROM #Programs
ORDER BY
Day1Date
I have tried the following but this only captures the first set of records
(the count):
INSERT INTO #temp EXECUTE StoredProc
I cannot change the stored procedure. Is there a way to get the second set
into a temprary table (or even permanent table, table variable etc) ?This is a multi-part message in MIME format.
--=_NextPart_000_0067_01C3C3BB.8715A830
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Consider using an output parameter to store the count:
-- inside the called proc
SELECT @.count = COUNT(*)
FROM #Programs
-- inside the calling proc
declare @.count int
INSERT INTO #temp
EXECUTE StoredProc @.count output
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OSwOuR#wDHA.3116@.TK2MSFTNGP11.phx.gbl...
I have a stored procedure that returns two sets of records, and I want to
execute the procedure and insert the second set of records into a temporary
table outside the procedure.
The last lines of the stored procedure look like this:
--Return count of programs first
SELECT COUNT(*)
FROM #Programs
--Return programs
SELECT *
FROM #Programs
ORDER BY
Day1Date
I have tried the following but this only captures the first set of records
(the count):
INSERT INTO #temp EXECUTE StoredProc
I cannot change the stored procedure. Is there a way to get the second set
into a temprary table (or even permanent table, table variable etc) ?
--=_NextPart_000_0067_01C3C3BB.8715A830
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Consider using an output parameter to =store the count:
-- inside the called =proc
SELECT @.count =3D =COUNT(*)FROM #Programs
-- inside the calling =proc
declare @.count int
INSERT INTO #temp
EXECUTE StoredProc @.count output
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laurence Neville" wrote in message news:OSwOuR#wDHA.3116=@.TK2MSFTNGP11.phx.gbl...I have a stored procedure that returns two sets of records, and I want toexecute the procedure and insert the second set of records into a temporarytable outside the procedure.The last lines of the =stored procedure look like this:--Return count of programs =firstSELECT COUNT(*)FROM #Programs--Return programsSELECT *FROM #ProgramsORDER BYDay1DateI have tried the following but =this only captures the first set of records(the count):INSERT =INTO #temp EXECUTE StoredProcI cannot change the stored procedure. Is there =a way to get the second setinto a temprary table (or even permanent table, =table variable etc) ?

---

--=_NextPart_000_0067_01C3C3BB.8715A830--

Getting only the first related result (with complications).

Here is the first table:
CREATE TABLE "Games"
("ID" int,
"Title" varchar(30),
"Popularity" int)
Here is the second table:
CREATE TABLE "Related"
("ID" int,
"rID" int)
Here is the data for the first table:
INSERT INTO Games (id, title, popularity) VALUES (1, 'Tag Dodgeball', 10)
INSERT INTO Games (id, title, popularity) VALUES (2, 'Freeball', 10)
INSERT INTO Games (id, title, popularity) VALUES (3, 'Doctor Dodgeball', 10)
INSERT INTO Games (id, title, popularity) VALUES (4, 'Kickball', 8)
INSERT INTO Games (id, title, popularity) VALUES (5, 'Fooseball', 8)
INSERT INTO Games (id, title, popularity) VALUES (6, 'Basketball', 7)
INSERT INTO Games (id, title, popularity) VALUES (7, 'Knockout', 6)
Here is the data for the second table:
INSERT INTO Related (ID, rID) VALUES (1,2)
INSERT INTO Related (ID, rID) VALUES (2,1)
INSERT INTO Related (ID, rID) VALUES (1,3)
INSERT INTO Related (ID, rID) VALUES (2,3)
INSERT INTO Related (ID, rID) VALUES (3,1)
INSERT INTO Related (ID, rID) VALUES (3,2)
INSERT INTO Related (ID, rID) VALUES (6,7)
INSERT INTO Related (ID, rID) VALUES (7,6)
Now, lets say a person wants to grab the top three results:
SET ROWCOUNT 3
Select * from Game ORDER BY Popularity
Result Set:
1 Tag Dodgeball 10
2 Freeball 10
3 Dr. Dodgeball 10
But,what if this person doesn't want to play three related games in a row?This is my dilemma. How do I get the following result set:
1 Tag Dodgeball 10
2 Kickball 8
3 Fooseball 8
Therelated table tells which games are related to which, but how do I getthe select statement to realize that it should not select any gamesthat are related once it already has one in the result set?
I can't use distinct, b/c if I do, any games which don't have a relationship are eliminated, as their rID is null.
Pseudo-Code might look something like this:
SET ROWCOUNT 3
Select * from Game ORDER BY Popularity (where ID !=rID)
Please help. I've banging my head against this one for quite some time.
Respectfully,
David.

Cheesy way of doing this, assuming you never get more than 1000 games...

SELECT g.*

FROM Games g

JOIN(SELECT*FROM relatedUNIONSELECT id,idFROM games) rON g.id=r.id

JOIN games g2ON g2.id=r.rid

GROUPBY g.ID,g.title,g.popularity

HAVINGmax(g2.popularity*1000-g2.id)% 1000= 1000-g.id

ORDERBY popularityDESC,ID

If you need more games, you can change the three 1000's to whatever number you need. There's a "better" way, but this was fast and easy.