"Max row" in each group

I'm having trouble developing a query that will group on a given set of
columns and, for each group, will also display the other columns in the
"max row" for that group. Let me illustrate what I mean:
CREATE TABLE [dbo].[GroupTest] (
[testID] [int] IDENTITY (1, 1) NOT NULL ,
[office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startDate] [datetime] NOT NULL ,
[status] [int] NOT NULL ,
[endDate] [datetime] NULL ,
[reportDate] [datetime] NOT NULL ,
[amount] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupTest] ADD
CONSTRAINT [PK_GroupTest] PRIMARY KEY CLUSTERED
(
[testID]
) ON [PRIMARY]
GO
Each row in this table represents a status update in an issue tracking
system. (The actual table has more columns. This is a subset for
illustrative purposes.) None of the columns is unique other than testID.
I am being asked to produce a query for each combination of {office,
startDate} will include the row matching that combination that has the
greatest value in the status column. So if office='ABC' and
startDate='2005-11-30' and there are two rows matching these values, one
with status=3 and one with status=4, the query should include the row
having status=4.
If the combination {office, startDate, status} were unique in this
table, the following query would do the job:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
order by G0.office, G0.startDate
But that's not the case. I asked the customer what to do. For their
purposes, the report will be fine if it can show them an "illustrative"
row--in other words, if more than one row in the table matches a given
combination {office, startDate, status}, the query should return one of
them arbitrarily. But I'm not figuring out how to do that. I guess I'd
like to figure out how to retrieve the "maximum row" for each {office,
startDate} combination, or something similar. Can anyone help?
I could punt and go with just displaying the maximum over each of the
columns on the right-hand side (status, endDate, reportDate, amount),
each taken independently:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus,
max(endDate) maxEndDate,
max(reportDate) maxReportDate,
max(amount) maxAmount
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
AND G0.endDate = G1.maxEndDate
AND G0.reportDate = G1.maxReportDate
AND G0.amount = G1.maxAmount
order by G0.office, G0.startDate
But the rows produced by that query aren't "coherent": For a given
{office, startDate} combination, the status could be from one row, the
startDate could be from another, the endDate from another, etc. Each row
in my query should match an actual row in the table.I've moved on from my previous question, having figured out an approach.
However, I'm stuck on that approach because I'm getting unexpected
results. I'm asking about that in a new thread.

"group by" by excluding prefix

Suppose there is a table containing these recodes.

country
---
CON_CHN
CON_JAP
JAP
CON_CHN

When I use the following sql:
select country, count(*) as num from table group by country

the normal result will be:
country num
-----
CON_CHN2
CON_JAP 1
JAP1

However, my desired result is as follows:
country num
------
CON_CHN2
CON_JAP 2

How can I re-write my SQL? Or any other methods to do that?Is the length of the string (prefix or sufix) fixed? Can the string be
something like CON_CANA ? Also, it would help if you provided sample
scripts...

MC

"littlebeam" <cyrus.lan@.gmail.com> wrote in message
news:1144831660.591396.269760@.e56g2000cwe.googlegr oups.com...
> Suppose there is a table containing these recodes.
> country
> ---
> CON_CHN
> CON_JAP
> JAP
> CON_CHN
> When I use the following sql:
> select country, count(*) as num from table group by country
> the normal result will be:
> country num
> -----
> CON_CHN 2
> CON_JAP 1
> JAP 1
> However, my desired result is as follows:
> country num
> ------
> CON_CHN 2
> CON_JAP 2
> How can I re-write my SQL? Or any other methods to do that?|||If your prefix is always "CON_" then this should work

SELECT Replace(country, 'CON_',''), COUNT(Replace(country, 'CON_',''))
from myTable
Group by Replace(country, 'CON_','')

Markus

"Did you mean" functionality how ?

Hi all, I do not if this is the right group to place my question.
Anyway here is:
All of you know about the "Did you mean:" functionality that Google
shows when you type the wrong word, or if there is a better combination
of words to performe the search, right?
That is what i am talking about:
"Did you mean" functionality >> "If it calculates that you're likely to
generate more relevant search results with an alternative spelling, it
will ask "Did you mean: (more common spelling)?". Clicking on the
suggested spelling will launch a Google search for that term"
well, my question is if is easy/possible to implement this funcionality
using SQL F-T indexation. I mean, is there any command that search
for"similar" words in the catalog... so I could implement something
similar than ""Did you mean:"
Thanks
Alberto
This is a fuzzy type of a search. Basically what Google does is executes two
searches. The first is a regular search against the word as spelled and then
a search against a dictionary of words using something like Levenstein edit
distance. If it detects that there are words that are similar to your search
argument and searches on these words would result in a large number of hits
the top alternate fuzzy word is suggested as a "did you mean" entry.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1131020654.483565.189570@.g43g2000cwa.googlegr oups.com...
> Hi all, I do not if this is the right group to place my question.
> Anyway here is:
> All of you know about the "Did you mean:" functionality that Google
> shows when you type the wrong word, or if there is a better combination
> of words to performe the search, right?
> That is what i am talking about:
> "Did you mean" functionality >> "If it calculates that you're likely to
> generate more relevant search results with an alternative spelling, it
> will ask "Did you mean: (more common spelling)?". Clicking on the
> suggested spelling will launch a Google search for that term"
> well, my question is if is easy/possible to implement this funcionality
> using SQL F-T indexation. I mean, is there any command that search
> for"similar" words in the catalog... so I could implement something
> similar than ""Did you mean:"
> Thanks
> Alberto
>
|||I understand...thanks for your help!
regards
Alberto

encrypt database

Hi,
Sorry for the message in spanish I confused the group.
Is there a way to encrypt a database (tables, columns, etc.) ? I want that
nobody can see the data model of my application
Probably it's impossible to deny access to the database, but at least if
someone is trying to copy my design he/she will see the name of objects,
columns, and others encrypted. (For example instead of see the table
ARTICLE, see symbols ="!!$%&/- )
I'll appreciate your comments.
Edmundo J. Davila
You can assign certificates and encryption keys under the security
folder of a database in SQL Server Management Studio, however, these
only encrypt the data stream that is sent from a SQL Server instance
to any SQL Server Agents.
If you install SQL Server to run from inside of Microsoft Virtual
Server, then the entire database file will be stacker compressed which
would be unreadable to human eyes. But, with any kind of encryption,
SQL Server will have to spend time decrypting fields as it walks any
kind of search and this will cause a significant performance hit.

encrypt database

Hi,
Sorry for the message in spanish I confused the group.
Is there a way to encrypt a database (tables, columns, etc.) ? I want that
nobody can see the data model of my application
Probably it's impossible to deny access to the database, but at least if
someone is trying to copy my design he/she will see the name of objects,
columns, and others encrypted. (For example instead of see the table
ARTICLE, see symbols ="!!°$%&/- )
I'll appreciate your comments.
Edmundo J. DavilaYou can assign certificates and encryption keys under the security
folder of a database in SQL Server Management Studio, however, these
only encrypt the data stream that is sent from a SQL Server instance
to any SQL Server Agents.
If you install SQL Server to run from inside of Microsoft Virtual
Server, then the entire database file will be stacker compressed which
would be unreadable to human eyes. But, with any kind of encryption,
SQL Server will have to spend time decrypting fields as it walks any
kind of search and this will cause a significant performance hit.