How to select specific record.

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 select specific record.

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-24 : 16:02:45

Group_Code *****Station_nbr*****Beg_Eff_Date*****End_eff_date


00000002      D01G00733          1/1/2007          8/31/2007
00000002N     D01G00733          4/1/2007          8/31/2007
00000002W     D01G00733          1/1/2007          12/31/2007

For the report I just want to be able to pick up the first and the last line.

Using dbo.Station_group Table

Thank you for the help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-24 : 16:03:24

and how is first and last determined?
order is on what column?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-24 : 16:31:26

[code] If (@begdate <> '1900-01-01')
Select @Records = Count(*)
From TIES_Temp.dbo.station_group a, TIES_Temp.dbo.station_group b
Where (a.group_code not like '%W')
and (b.group_code not like '%N')
and (b.group_code = a.group_code)
and (a.station_nbr = @stationnumber)
and (a.station_nbr = b.station_nbr)
and (b.End_eff_date<>@endeffdate)
and (a.End_eff_date<>@endeffdate)[/code]

I am not sure if this answer your question...But this is what I have done so far to select those record. This is the If statement for a cursor.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-24 : 16:32:37

Order is on First Row ---Primary Group Code is the one without any letters at the end. That is the Key Record.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-24 : 17:13:55

Something like this maybe?

SELECT 
	Station_group.*
FROM 
	dbo.Station_group
INNER JOIN
	(
		SELECT MIN(Group_Code) AS Group_Code
		FROM dbo.Station_group

		UNION ALL

		SELECT MAX(Group_Code)
		FROM dbo.Station_group
	) AS Temp
	ON Station_group.Group_Code = Temp.Group_Code

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-24 : 17:26:11

Thanks Lamprey... Inner Join - What table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-24 : 17:28:55

His query is using a derived table in the join:


		SELECT MIN(Group_Code) AS Group_Code
		FROM dbo.Station_group

		UNION ALL

		SELECT MAX(Group_Code)
		FROM dbo.Station_group

Derived tables are select statements. The result set that you get back from a select statement acts just like the data in a table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-24 : 17:31:11

I really don't know how to write that select statement.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-24 : 17:36:09

What's there to write? He already did it. Can't you just play with it until you understand it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-24 : 17:42:46

I am trying but I am getting an error such as Server:

Msg 107, Level 16, State 2, Line 1
The column prefix 'Station_nbr' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Station_nbr' does not match with a table name or alias name used in the query.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-24 : 17:48:12

SELECT
Station_group.* (What's this Station Group *** Is this a column?)
FROM
dbo.Station_group
INNER JOIN
(
SELECT MIN(Group_Code) AS Group_Code
FROM dbo.Station_group

UNION ALL

SELECT MAX(Group_Code)
FROM dbo.Station_group
) AS Temp
ON Station_group.Group_Code = Temp.Group_Code

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-24 : 17:52:38

He's just returning all columns from station_group table. That's what * does.

As for your errors, you'd have to post exactly what you tried that got the error in order for us to help. If you don't recognize a column name, then make sure that it exists in the table, otherwise change it to what exists and makes sense. Remember that we can't see your system so you have to be very detailed on all of your posts in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 02:29:08

This is the fifth post where OP has absolutely no idea of his own code and table structure.

Again, are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-25 : 13:07:30

I am using SQL 2000. I am completeley new to this SQL world, I started to work on it 2 weeks ago. I hope this answer your question PESO.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-25 : 14:06:13

osirisa,

Please watch the attitude. It's extremely rude to ask people for help and then being snippy when they try. It makes people not want to help.

[quote user="osirisa"]
Msg 107, Level 16, State 2, Line 1
The column prefix 'Station_nbr' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Station_nbr' does not match with a table name or alias name used in the query.
[/quote]

Neither of the column prefixes you mention was in Lamprey's SQL, so it's obviously a mistake you caused. However, if you don't bother to post the SQL you're using, how are we supposed to help you find the error?

If you don't provide the information we need to help, we can't help. It's that simple. Your being rude isn't going to help that at all, and will in fact hurt you because people will quit trying to assist you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources