?
Solved

sql get latest unique

Posted on 2011-03-25
2
Medium Priority
?
285 Views
Last Modified: 2012-06-21
what to retrieve a unique count of records ... when an db update is processed an new copy of the record is actioned (to preserve this historic data) ... so for example, the same account details may appear 3 or 4 times already ...but only want to count the latest copy.

there is also a DateTimeStamp column available (when table created / add to db)
need to also group results by ColumnValue item (there will be a bunch of denominations) -- but the frequency each of those occur ... and then finally a grand total of Frequency at the bottom.

how do you script this in SQL please?


USE db1

SELECT COUNT(Index_ID) AS Frequency

FROM dbo.Table1
WHERE (MySearchCriteria_ID <> 123) AND .....
GROUP BY DenominationValue

then display at bottom:  GrandFreqTotal
0
Comment
Question by:amillyard
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35218557
Should be something like:


SELECT COUNT(distinct Index_ID) AS Frequency,SUM(GrandFreqTotal) as GrandFreqTotal
FROM dbo.Table1
WHERE MySearchCriteria_ID <> 123 AND .....
GROUP BY DenominationValue

0
 

Author Comment

by:amillyard
ID: 35227464
SUM(GrandFreqTotal) as GrandFreqTotal

the above line is not compiling - is this because there is an assumption there is column in table called:   GrandFreqTotal

the group by list -- how do I also display the column label  (or custom label) -- as currently there is just a set of figures (not easy to work out what each line is a result for).

i.e. the unique column value to be display (currently, the frequency of that value is being displayed)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question