compare values in table - 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

General SQL Server Forums

New to SQL Server Programming

compare values in table

Author

Topic

tracmonali
Starting Member

16 Posts

Posted - 2012-05-14 : 16:22:57

hello,
with table col as follows,
col1 col2 col3
Name1 addr1 11/11/2005
Name2 addr2 12/2/2005
name3 addr3 2/15/2006
Name4 addr4 3/28/2006
Name5 addr5 6/6/2010
now if @myvar = 03/01/2006, it should ignore all values greater than @myvar (aka 3/28/2006 and 6/6/2010) but pick up max of the values lesser than @myvar (11/11/2005, 12/2/2005, 2/15/2006). so output after comparing should pick up 2/15/2006. thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 17:43:56

[code]SELECT TOP (1)
*
FROM
YourTable
WHERE
Col3 <= @myvar
ORDER BY
Col3 DESC[/code]Couple of questions:

1. If @myvar is exactly equal to a value in Col3, what do you want to do ? Ignore that or use that? The <= that I have used will pick up that as the value to report.

2. I am assuming that data type of Col3 is DATE/DATETIME type. If not, the behavior would not be correct.

3. This will always return you just one row. Is that the requirement, or do you need to return one row for each group (of some sort)? If you do, what is the grouping criterion? You have not shown the grouping columns in the sample data.

tracmonali
Starting Member

16 Posts

Posted - 2012-05-14 : 18:06:28

Thanks sunitabeck. Yes col3 is datetime. If @myvar = col3 it should pick up and NOT ignore. Yes it should return 1 row. Well it should work for group of same emp ids. There is col of emp ids, and there are more than 1 row for same emp ids. Hence for every emp id it should return 1 row. The date col can be less or equal. but if it is less then it should pick up max of less value.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 19:35:41

quote:
Originally posted by tracmonali

Thanks sunitabeck. Yes col3 is datetime. If @myvar = col3 it should pick up and NOT ignore. Yes it should return 1 row. Well it should work for group of same emp ids. There is col of emp ids, and there are more than 1 row for same emp ids. Hence for every emp id it should return 1 row. The date col can be less or equal. but if it is less then it should pick up max of less value.

If you have more than one Employee groups and if you need to return one from each employee group, the query I posted earlier would not be sufficient. In that case you would need something like this:

SELECT
	EmployeeGroupId,
	Col1,
	Col2,
	Col3
FROM
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY EmployeeGroupId ORDER BY Col3 DESC) AS RN
	FROM
		YourTable
)s
WHERE RN = 1;

tracmonali
Starting Member

16 Posts

Posted - 2012-05-15 : 10:37:57

SunitaBeck, row() doesn't work in sql 2000. any suggestions?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-15 : 10:55:10

Your can try this - I am not very familiar with SQL 2000, so....

SELECT
	a.EmployeeGroupId,
	a.Col1,
	a.Col2,
	a.Col3
FROM
	YourTable a
	INNER JOIN 
	(
		SELECT
			EmployeeGroupId,
			MAX(Col3) AS Col3
		FROM
			YourTable
		GROUP BY EmployeeGroupId
	) b ON 
	a.EmployeeGroupId = b.EmployeeGroupId AND 
	a.Col3 = b.Col3;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 22:08:17

is there a chance like you'll have multiple records with same empid,date values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tracmonali
Starting Member

16 Posts

Posted - 2012-05-16 : 09:50:56

yes, multiple records with same empid, but diff date values

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-16 : 09:57:59

quote:
Originally posted by tracmonali

yes, multiple records with same empid, but diff date values

I think Visakh is asking whether there will be two rows with the SAME empid and the SAME date values. For example,


EmpId    Col1   Col2    Col3
1234     Name1  addr1   11/11/2005
1234     Name2  addr2   11/11/2005

If that can happen, and if that happened to be the MAX date, the query I posted on 05/15/2012 : 10:55:10 would pick up both rows

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources