Figuring out the right query

[booner]

I have a table (user)

ip, ssn, date,name

Each time the user does something, I record that information in the table.
I'd like get a list of the last 4 entries for distinct ssn.

I started with this ... but cannot seem to get only the top 4 distinct SSNs.

select top 4,ssn,name from user where ip='<ip>' order by date desc

Any insight you can provide would be most appreciated.

BBB

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There shouldn't be a comma after the 4 in the SELECT clause.

How about an example of the data you are querying and your expected
results?

Is the ip address set up like this in the WHERE clause:

WHERE ip = '192.0.0.168'

Depending on your data the same ssn,name could be showing up on the same
date. Does the date column also contain the time?

Have you tried the DISTINCTROW predicate?

SELECT DISTINCTROW top 4 ssn, ...

Some design details: "date" and "name" are bad column names. Date is a
VBA reserved word. Anyway, "Date for what?" Name is an SQL reserved
word. Again, "Name of what?"
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+ucf4echKqOuFEgEQJMxwCgwLq+pxWZ6HN7ByDWVYrHdm UT69oAn1O7
NKtg9hC3U++ALXmMSqkZVMwv
=wslw
-----END PGP SIGNATURE-----

booner wrote:

I have a table (user)

ip, ssn, date,name

Each time the user does something, I record that information in the table.
I'd like get a list of the last 4 entries for distinct ssn.

I started with this ... but cannot seem to get only the top 4 distinct SSNs.

select top 4,ssn,name from user where ip='<ip>' order by date desc

Any insight you can provide would be most appreciated.

[booner]

I had tried Distinct ... was not familiar with DistinctRow.

Understand on the names - was just trying to make the example more generic.

Example data:

IP,Date,LastName,SSN
1.1.1.2, 2/8/2006 3:54:41 PM,Smith,123456789
1.1.1.2, 2/8/2006 1:23:40 PM,Smith,123456789
1.1.1.2, 2/6/2006 3:23:21 PM,Jones,987654321
1.1.1.2,2/6/2006 5:23:34 PM,Jones,987654321
1.1.1.2,2/3/2006 9:23:21 AM,Public,111223333
1.1.1.2,2/3/2006 10:24:28 AM,Public,111223333
1.1.1.2,2/3/2006 8:17:39 AM,Johnson,321549876
1.1.1.2,2/3/2006 9:16:39 AM,Jones,231539676

So what I'm looking to get is
123456789,Smith
987654321,Jones
111223333,Public
231539676,Jones

Get the latest 4 unique SSN based on the Date.
"MGFoster" <[e-mail]> wrote in message
news:_%**************@newsread2.news.pas.earthlink .net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There shouldn't be a comma after the 4 in the SELECT clause.

How about an example of the data you are querying and your expected
results?

Is the ip address set up like this in the WHERE clause:

WHERE ip = '192.0.0.168'

Depending on your data the same ssn,name could be showing up on the same
date. Does the date column also contain the time?

Have you tried the DISTINCTROW predicate?

SELECT DISTINCTROW top 4 ssn, ...

Some design details: "date" and "name" are bad column names. Date is a
VBA reserved word. Anyway, "Date for what?" Name is an SQL reserved
word. Again, "Name of what?"
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+ucf4echKqOuFEgEQJMxwCgwLq+pxWZ6HN7ByDWVYrHdm UT69oAn1O7
NKtg9hC3U++ALXmMSqkZVMwv
=wslw
-----END PGP SIGNATURE-----

booner wrote:

I have a table (user)

ip, ssn, date,name

Each time the user does something, I record that information in the table. I'd like get a list of the last 4 entries for distinct ssn.

I started with this ... but cannot seem to get only the top 4 distinct SSNs.
select top 4,ssn,name from user where ip='<ip>' order by date desc

Any insight you can provide would be most appreciated.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT TOP 4 ssn, last_name
FROM table_name As T
WHERE [date] = (SELECT MAX([date]) FROM table_name
WHERE ssn = T.ssn)
ORDER BY [date] DESC
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+uzv4echKqOuFEgEQIlHwCg6T0LmI9tTqiPzeLIvkDK0Z e8P8sAn0Gs
WJmvQAE38iTf7ZgBvLJntYy7
=UJY/
-----END PGP SIGNATURE-----

booner wrote:

I had tried Distinct ... was not familiar with DistinctRow.

Understand on the names - was just trying to make the example more generic.

Example data:

IP,Date,LastName,SSN
1.1.1.2, 2/8/2006 3:54:41 PM,Smith,123456789
1.1.1.2, 2/8/2006 1:23:40 PM,Smith,123456789
1.1.1.2, 2/6/2006 3:23:21 PM,Jones,987654321
1.1.1.2,2/6/2006 5:23:34 PM,Jones,987654321
1.1.1.2,2/3/2006 9:23:21 AM,Public,111223333
1.1.1.2,2/3/2006 10:24:28 AM,Public,111223333
1.1.1.2,2/3/2006 8:17:39 AM,Johnson,321549876
1.1.1.2,2/3/2006 9:16:39 AM,Jones,231539676

So what I'm looking to get is
123456789,Smith
987654321,Jones
111223333,Public
231539676,Jones

Get the latest 4 unique SSN based on the Date.
"MGFoster" <[e-mail]> wrote in message
news:_%**************@newsread2.news.pas.earthlink .net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There shouldn't be a comma after the 4 in the SELECT clause.

How about an example of the data you are querying and your expected
results?

Is the ip address set up like this in the WHERE clause:

WHERE ip = '192.0.0.168'

Depending on your data the same ssn,name could be showing up on the same
date. Does the date column also contain the time?

Have you tried the DISTINCTROW predicate?

SELECT DISTINCTROW top 4 ssn, ...

Some design details: "date" and "name" are bad column names. Date is a
VBA reserved word. Anyway, "Date for what?" Name is an SQL reserved
word. Again, "Name of what?"
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+ucf4echKqOuFEgEQJMxwCgwLq+pxWZ6HN7ByDWVYrHdm UT69oAn1O7
NKtg9hC3U++ALXmMSqkZVMwv
=wslw
-----END PGP SIGNATURE-----

booner wrote:

I have a table (user)

ip, ssn, date,name

Each time the user does something, I record that information in the
table.
I'd like get a list of the last 4 entries for distinct ssn.

I started with this ... but cannot seem to get only the top 4 distinct
SSNs.
select top 4,ssn,name from user where ip='<ip>' order by date desc

Any insight you can provide would be most appreciated.

[booner]

I tried that. In access it prompts me to enter a parameter value for
last_name. I'm looking to perform this query programmatically - and when I
do it just fails:

ERROR: [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 1.
"MGFoster" <[e-mail]> wrote in message
news:%s**************@newsread2.news.pas.earthlink .net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT TOP 4 ssn, last_name
FROM table_name As T
WHERE [date] = (SELECT MAX([date]) FROM table_name
WHERE ssn = T.ssn)
ORDER BY [date] DESC
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+uzv4echKqOuFEgEQIlHwCg6T0LmI9tTqiPzeLIvkDK0Z e8P8sAn0Gs
WJmvQAE38iTf7ZgBvLJntYy7
=UJY/
-----END PGP SIGNATURE-----

booner wrote:

I had tried Distinct ... was not familiar with DistinctRow.

Understand on the names - was just trying to make the example more generic.
Example data:

IP,Date,LastName,SSN
1.1.1.2, 2/8/2006 3:54:41 PM,Smith,123456789
1.1.1.2, 2/8/2006 1:23:40 PM,Smith,123456789
1.1.1.2, 2/6/2006 3:23:21 PM,Jones,987654321
1.1.1.2,2/6/2006 5:23:34 PM,Jones,987654321
1.1.1.2,2/3/2006 9:23:21 AM,Public,111223333
1.1.1.2,2/3/2006 10:24:28 AM,Public,111223333
1.1.1.2,2/3/2006 8:17:39 AM,Johnson,321549876
1.1.1.2,2/3/2006 9:16:39 AM,Jones,231539676

So what I'm looking to get is
123456789,Smith
987654321,Jones
111223333,Public
231539676,Jones

Get the latest 4 unique SSN based on the Date.
"MGFoster" <[e-mail]> wrote in message
news:_%**************@newsread2.news.pas.earthlink .net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There shouldn't be a comma after the 4 in the SELECT clause.

How about an example of the data you are querying and your expected
results?

Is the ip address set up like this in the WHERE clause:

WHERE ip = '192.0.0.168'

Depending on your data the same ssn,name could be showing up on the same
date. Does the date column also contain the time?

Have you tried the DISTINCTROW predicate?

SELECT DISTINCTROW top 4 ssn, ...

Some design details: "date" and "name" are bad column names. Date is a
VBA reserved word. Anyway, "Date for what?" Name is an SQL reserved
word. Again, "Name of what?"
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+ucf4echKqOuFEgEQJMxwCgwLq+pxWZ6HN7ByDWVYrHdm UT69oAn1O7
NKtg9hC3U++ALXmMSqkZVMwv
=wslw
-----END PGP SIGNATURE-----

booner wrote:

I have a table (user)

ip, ssn, date,name

Each time the user does something, I record that information in the

table.

I'd like get a list of the last 4 entries for distinct ssn.

I started with this ... but cannot seem to get only the top 4 distinct

SSNs.

select top 4,ssn,name from user where ip='<ip>' order by date desc

Any insight you can provide would be most appreciated.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Change the names of the columns & tables I used in my example to the
actual names in your database.

The table_name in the main query and the subquery should be the same
table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+zZG4echKqOuFEgEQI9oQCfeuKOpiI1C+GAF3h/7qF8lAPthuEAoMDr
oxZRP1m4NeDfL9qJjMeUif57
=l3zY
-----END PGP SIGNATURE-----

booner wrote:

I tried that. In access it prompts me to enter a parameter value for
last_name. I'm looking to perform this query programmatically - and when I
do it just fails:

ERROR: [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 1.
"MGFoster" <[e-mail]> wrote in message
news:%s**************@newsread2.news.pas.earthlink .net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT TOP 4 ssn, last_name
FROM table_name As T
WHERE [date] = (SELECT MAX([date]) FROM table_name
WHERE ssn = T.ssn)
ORDER BY [date] DESC
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+uzv4echKqOuFEgEQIlHwCg6T0LmI9tTqiPzeLIvkDK0Z e8P8sAn0Gs
WJmvQAE38iTf7ZgBvLJntYy7
=UJY/
-----END PGP SIGNATURE-----

booner wrote:

I had tried Distinct ... was not familiar with DistinctRow.

Understand on the names - was just trying to make the example more
generic.
Example data:

IP,Date,LastName,SSN
1.1.1.2, 2/8/2006 3:54:41 PM,Smith,123456789
1.1.1.2, 2/8/2006 1:23:40 PM,Smith,123456789
1.1.1.2, 2/6/2006 3:23:21 PM,Jones,987654321
1.1.1.2,2/6/2006 5:23:34 PM,Jones,987654321
1.1.1.2,2/3/2006 9:23:21 AM,Public,111223333
1.1.1.2,2/3/2006 10:24:28 AM,Public,111223333
1.1.1.2,2/3/2006 8:17:39 AM,Johnson,321549876
1.1.1.2,2/3/2006 9:16:39 AM,Jones,231539676

So what I'm looking to get is
123456789,Smith
987654321,Jones
111223333,Public
231539676,Jones

Get the latest 4 unique SSN based on the Date.