HOW CAN I DISPLAY ALL TOP 2 RECORDS IN THE DATBASE

  • Thread starter Thread starter heri.carandang
  • Start date Start date
H

heri.carandang

HOW CAN I DISPLAY ALL TOP 2 RECORDS IN THE DATABASE IF IHAVE 3000
RECORDS ?
FILE IS SORTED IN STARTDATE

IF A CERTAIN EMPLOYEE HAVE 2 OR MORE RECORDS IT SHOULD DISPLAY ONLY THE
FIRST TOP 2 RECORDS OF THE EMPLOYEE.

EX :

NAME TOP1 TOP2

JOHN DOE 10/10/2006 10/25/2006
GAYLE 10/10/2006 10/30/2006
ROSE 10/30/2006
JAY 10/30/2006
HERI 10/25/2006
JAY 11/20/2006 11/25/2006

IF ONLY ONE RECORD IN THE DATABASE IT WILL SHOW IN TOP2

PLEASE HELP THANK YOU SO MUCH !!!
 
Dear Heri:

Doing this depends on what you mean by TOP 2. Is it the two oldest dated
rows? Since your example shows TOP1 dates consistently older than the TOP2
column, this is the best guess I can make.

Whatever "sort order" you use to base the sort order, it must be unique.
Otherwise, when there is a two (or more) way tie for second place, you're
going to get more than just 2 results. WHen there are 3 or more that tie
for first place, the results will show all of them, even if there are 100 or
1000 rows. That is because the definition of "top 2" is ambiguous when
there are such ties.

Please answer the above, and provide a query that shows everything you want
in the finished query but this feature. I'll try to modify that to do what
you want.

Tom Ellison
 
Hi TOm,

Can you please assist me on how to get the 2nd record in case there are
3 or more records of an employee, the query below gets the MAX and MIN
BasicSalary. However, my MIN Basic Salary is wrong because I should get
the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
more records and not the last Basic Salary of the Last Record.

How to GET the 2nd Row of Record in Case that There are 3 or more
records.

--------------------------------------------------------------------------------

This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.
Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...


SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,

(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND
@ToDate
ORDER BY startdate DESC) AS NEWBASIC,

******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD

(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND
@ToDate
ORDER BY startdate ASC) AS OLDBASIC

FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @FromDate AND
@ToDate
ORDER BY E.LastName
GO
 
Hi,


SELECT a.*

FROM myTable As a

WHERE a.primaryKey IN( SELECT TOP 2 b.primaryKey
FROM myTable As b
WHERE b.employeeNumber =
a.employee.Number
AND b.dateStamp BETWEEN starting
AND ending
ORDER BY b.salary DESC )

AND a.dateStamp BETWEEN starting AND ending




is a possible solution. If you have MS SQL Server 2005, you can rank the
records and take those where their rank <= 2.


Hoping it may help,
Vanderghast, Access MVP
 
Dear Heri:

Here is a possible, and relatively simple approach that may be what you're
wanting.

If you do this:

SELECT TOP 1
..
FROM
(SELECT TOP 2
..
..
..
ORDER BY StartdateDESC)
ORDER BY Startdate ASC

This would give you the second oldest. This will work only if there are NO
TIES in the Startdate column.

You chose not to answer the questions I asked, which are fundamental to
avoiding possible problems with doing this.

Tom Ellison
 
Back
Top