[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Find a speciifc row number of an SQL table by search data within the table

Posted on 2014-12-30
5
Medium Priority
?
144 Views
Last Modified: 2015-01-02
I need to identify row numbers by data in column "Hour"

Hour     Row Number
0                     0
1                     1
12                   2
13                   3
14                   4

For example, look up the row number of hour 12 should return 3, the row numbers are in numeric order starting at 0, the hours could be random on any row.  I can not use ID numbers to solve this as the ID numbers could be anything.

Thanks for the help.
0
Comment
Question by:SweetingA
[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
5 Comments
 

Author Comment

by:SweetingA
ID: 40524874
I should mention, i am searching in vb express 2010 and looking into SQL server 2012
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 40524910
Hi SweetingA;

Assuming that Hours is a Integer data type in the database and it only appears once in the database this should return the [Row Number] column.

Dim hour As Integer = 12
'' TableNameHere needs to be replaced with the TableName from the database
Dim sql As String = "SELECT [Row Number] FROM TableNameHere WHERE Hours = + & hour

Open in new window

0
 
LVL 3

Expert Comment

by:Ali HND
ID: 40524923
Dim count As Integer
con.Open()
query = "SELECT COUNT(Hour) AS Hourcount FROM Yourtable WHERE Hour = 12 "

cmd = New SqlCommand(query, con)

count = cmd.ExecuteScalar 
MsgBox(count)

con.Close()

Open in new window

0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 40524927
Reposting code there was a " missing in my original post.

Dim hour As Integer = 12
'' TableNameHere needs to be replaced with the TableName from the database
Dim sql As String = "SELECT [Row Number] FROM TableNameHere WHERE Hours = " + & hour

Open in new window

0
 

Expert Comment

by:NonComposMentis
ID: 40524951
Absent a built in function to give you the row number (such as is provided in SQL Server and Oracle), what I would probably do is create a temporary table that looks like the explanatory table you started out with.  In T-SQL that would be

DECLARE @TempHours TABLE
(Hour INT, "Row Number" INT IDENTITY(1,1)); //i.e. start at 1 and increment by 1 automatically

The keywords DECLARE and IDENTITY are T-SQL keywords and will probably be different in your dialect.  I would start by searching for "auto increment".

Then dump all your hours into the Hour column

INSERT INTO @TempHours (Hour)
SELECT Hour FROM YourTableNameHere; // to use Fernando's table name notation

Assuming the selection happens in row order - not guaranteed, but what else can you assume(?) - you will have almost the table you presented in your question, and can query it from there.  The difference is that the row numbers start at 1 instead of 0.  You can make it 0 if you want, but 1 base row numbers seem to be what you are looking for.

For example,

SELECT [Row Number] FROM @TempHours
WHERE Hour = 12;
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

649 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