?
Solved

sql get latest unique

Posted on 2011-03-25
2
Medium Priority
?
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 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