How do I select the items based on their adjacent equality?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

In the below table,

    DECLARE @t1 TABLE(id INT,NAME VARCHAR(MAX))
    INSERT INTO @t1 VALUES(1,'test')
    INSERT INTO @t1 VALUES(2,'test')
    INSERT INTO @t1 VALUES(3,'best')
    INSERT INTO @t1 VALUES(4,'rest')
    INSERT INTO @t1 VALUES(5,'rest')

how can I select the records that have equals values coming adjacently? That is I am running an SQL job. It should take the adjacent equal value  count for its processing. In the below table, first time the job is running it should take count as 2 the 2nd time job is running it should be 1. Third time it should be 2. What is the query that will fetch the cont of equal records for name column? In case no equal columns it should be 1. Please help

Posted 9-Apr-15 23:13pm

mayooran99

Add a Solution

Comments

Joan Magnet 10-Apr-15 5:23am

Do you mean?

select count(*) from table where id=@id_act-1 or id=@id_act+1

mayooran99 10-Apr-15 5:27am

what is @id_act here?

Joan Magnet 10-Apr-15 5:33am

the new id added value

mayooran99 10-Apr-15 5:38am

so from my table, it is @t1.id right?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-04-09T23:24:00

Solution 1

Try this:

SQL

SELECT [NAME], COUNT([NAME]) AS CountOfNames
FROM @t1
GROUP BY [NAME]

Second column returns a count of name.

Posted 9-Apr-15 23:24pm

Maciej Los

Comments

mayooran99 10-Apr-15 5:28am

I dont want the count of all items. I need the count for only the 1st set of equal items. Because the job needs the count of top n items that are equal

Maciej Los 10-Apr-15 5:39am

Above query does not return the count of all items. It returns the count for each group: test - 2, best - 1, rest - 2. Try!