I forgot what my query does. Can someone explain it?

[LordJezo]

It's been month's since i wrote this thing and have no idea what it does or why, other then it selects a bunch of distinct rows from a table with many duplicate entries.

SELECT First([import_table].[server_name]) AS [server_name Field], First([import_table].[filesystem]) AS [filesystem Field], First([import_table].[filesystem_size]) AS [filesystem_size Field], First([import_table].[filesystem_used]) AS [filesystem_used Field] INTO nodupe_fs_table
FROM import_table
GROUP BY [import_table].[server_name], [import_table].[filesystem], [import_table].[filesystem_size], [import_table].[filesystem_used]
HAVING (((Count(import_table.server_name))>0) AND ((Count(import_table.filesystem_used))>0));


How does that go through a table and make sure it displays only 1 of each line, getting rid of all the extra duplicates?

 

[Mile-O]

It uses the First function to return only one record.

 

[LordJezo]

So what it does.. if I have a bunch of duplicate entries in the selected fields, it will only return the first one of each set of duplicates?

That's what it does when I run it anyway.

To make it clearer.. I have a large table with many entries. It seems like the query is selecting only certain fields from that large table, say, 4 out of the 6. With those 4 selected fields there will be duplicate lines. That query will use the FIRST function to only select the first row of each duplicating set.

Right?

 

[Brianwarnock]

I can't work out if you're asking us or telling us

 

[LordJezo]

I can't work out if you're asking us or telling us

More of an asking. Using what the first response said to try to figure out what it does.

I have no idea how its only selecting distinct rows, only that it some how is.

 

[Brianwarnock]

Mile told you how.
Access help calculations in queries then on aggregate functions or similar will explain further, or maybe not

 

[Brianwarnock]

Just to prove I'm not trying to be unhelpful below is the quote from Access help

First and Last functions

You use the First or Last functions to return the first or last record in the group you are performing calculations on. These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions.

 

[LordJezo]

Just to prove I'm not trying to be unhelpful below is the quote from Access help

First and Last functions

You use the First or Last functions to return the first or last record in the group you are performing calculations on. These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions.

Okay, so the SELECT statement is considered the calculation:

SELECT First([import_table].[server_name]) AS [server_name Field], First([import_table].[filesystem]) AS [filesystem Field], First([import_table].[filesystem_size]) AS [filesystem_size Field], First([import_table].[filesystem_used]) AS [filesystem_used Field] INTO nodupe_fs_table

It's selecting the FIRST row of each duplicated set it finds in the table? So it goes line by line grouping into sets, and returning the first entry of each set?

 

[Len Boorman]

The SQL will execute as follows


First the From Clause (a cartesian product)
Then It will do the Group
Then is will do the Having (like a where clause but applied to groups)

and finally

The Select clause

Len B