Trying to select unique records from a database. A combination of 2 variables make the record unique. Need to export all rows of the return. I have below rudimentary sql skills (select/where) and cannot figure this one out. The database has ~ 180k rows, of which there are only ~ 50k unique records.
The data is on a single table, 15 seperate columns. It is a table containing network router data. The existing table set up shows the historical progression of changes/updates to these routers. I am trying to determine the latest records of the routers in question. Since some of the IP addresses have been used multiple times (moved from site A to site B for example), there are multiple dupe records that I do not want.
The two fields I need to use to identify unique records are:
serial_IP and site_id
In addition to that, I would also like to know how to format "max date" query, so that I get the latest information. I can do that manually if I must via a sort when I export the data, but it would be nice to know how to do that in the future.
thanks
noclueAs those two columns make a unique key on the table, there is possibility of use of a DISTINCT keyword, such as
SELECT DISTINCT serial_IP, site_id FROM your_table;
However, regarding your next request (selecting records with the latest date), there's need to use a subquery:SELECT DISTINCT t.serial_IP, t.site_id
FROM your_table t
WHERE t.date_column = (SELECT max(t1.date_column)
FROM your_table t1
WHERE t1.serial_IP = t.serial_IP
AND t1.site_id = t.site_id
);The DISTINCT keyword might, or might not be needed in this case: if table has only one record per "date_column", you won't need that. If there are multiple records per every "date_column" value, you'll still need it to eliminate multiple records.
Showing posts with label export. Show all posts
Showing posts with label export. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Need Answer PLS - excel export file size changed on re-save
I have a report that ran and returns a large amount a data. When I export the report to excel it takes 3 minutes to export. The file size when I open the xls file is 24.1MB. I choose save as and give it another name ... then open the xls I just saved and the size is 9.79MB. Why is the file size different on the re-saved file than the originally exported file? This was reported by a user and I didnt believe the file size would change until I tried it myself ...sorry ... forgot to add that I am running RS2005 sp1|||If you have a lot of
string values in the report that can be represented as single byte strings,
that’s probably where Excel is making a difference. In Reporting Services we
always write strings as 2-byte Unicode, but Excel will always try to compress to
single byte when possible.
Subscribe to:
Posts (Atom)