?
Solved

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

Posted on 2014-12-30
5
Medium Priority
?
143 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 63

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 63

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
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: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

801 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